How to get goup concat of count value using sub query

Posted on

Question :

I’m trying to get group concat of count value
here table below I want this structure:

| y    | m          | monthName                               | RowCount  | model |   
| 2019 | 2,6,8,9,11 | February,June,August,September,November | 2,1,1,1,1 | Magnum |
| 2019 | 5          | May                                     | 2         | Stella |

But I got this structure :

| y    | m          | monthName                               | RowCount  | model |   
| 2019 | 2,6,8,9,11 | February,June,August,September,November | 6         | Magnum |
| 2019 | 5          | May                                     | 2         | Stella |

Here my Query :

    set @p_dealer = 0;
    set @p_employee = 0;
    select YEAR(cast(c.entryDate as date)) AS y,
        group_concat(distinct MONTH(cast(c.entryDate as date))) AS m, 
        group_concat(distinct MONTHNAME(cast(c.entryDate as date))) as monthName,
        COUNT(1) as RowCount,               
        (select st.sModel from tbl_stock st where st.sId=c.sId limit 1) as model
        from tbl_customer c
        where YEAR(cast(c.entryDate as date))=2019 -- YEAR(cast(now() as date)) 
        and ifnull((case @p_dealer when 0 then @p_dealer else c.did end),0)=@p_dealer
        and ifnull((case @p_employee when 0 then @p_employee else c.eid end),0)=@p_employee
        GROUP BY model;

My table information is:
stock table (tbl_stock)
enter image description here

and customer table (tbl_customer)
enter image description here

Answer :

If you use

GROUP BY model;

the result will be one line per each model.

You shall make a sub query (eliminating the group concat of month and monthname in your query) with this

GROUP by left(entrydate, 7), model;

and then group concat the month, monthname and count columns in another select.

Leave a Reply

Your email address will not be published. Required fields are marked *