Search code examples
mysqlgroup-concat

MySQL : GROUP_CONCAT the column and its count

I'm having a customer_status table where the column "id" is unique and cust_id is customer id and "status" is the current status of the customer.

The table is as follows: 

id       cust_id       status
1          1            NEW
2          2            NEW
3          1            VIEWED
4          1            NEW
5          1            VIEWED
6          1            NEW

Now my requirement is to show the status and the status count for each customer.

For example consider cust_id = 1 then output should be:

NEW : 3
VIEWED : 2

I tried using group_concat but couldnot succeed with displaying status and status count, seperated with colon.

Please help me out.

Solution

  • You can use CONCAT:

    select CONCAT(status,':',count(status)) from customer_status as result where cust_id = 1 group by status;