How to use sum () in group_concat ()?

Question

Question changed

Really wanted group_concat sums ...

Table: shops

+---------+--------+--------+ | shop_id | name | state | +---------+--------+--------+ | 0 | shop 0 | 5 | | 1 | shop 1 | 5 | | 2 | shop 2 | 5 | | 3 | shop 3 | 2 | +---------+--------+--------+ 

Table: items

 +------------+--------------+ | shop | item | quantity | +------------+--------------+ | 0 | 0 | 1 | | 0 | 1 | 2 | | 0 | 2 | 3 | | 1 | 0 | 1 | | 1 | 1 | 2 | | 1 | 2 | 3 | | 2 | 0 | 1 | | 2 | 1 | 2 | | 2 | 2 | 3 | | 3 | 0 | 1 | | 3 | 1 | 2 | | 3 | 2 | 3 | +------------+--------------+ SELECT state,SUM(i.quantity) total FROM shops s2 LEFT JOIN items i ON i.shop=s2.shopid WHERE state=5 GROUP by item result #1: +--------+---------+ | state | total | +--------+---------+ | 5 | 3 | +--------+---------+ | 5 | 6 | +--------+---------+ | 5 | 9 | +--------+---------+ But I would like the totals, like this: result #2: +--------+---------+---------+----------+ | state | total 0 | total 1 | total 2 | +--------+---------+---------+----------+ | 5 | 3 | 6 | 9 | +--------+---------+---------+----------+ or using group_concat() result #3 +--------+---------+ | state | totals | +--------+---------+ | 5 | 3,6,9 | +--------+---------+ 

I cannot get group_concat to capture the resulting column as a result of # 1

Thanks in advance

Answer 1

Found a way to do this:

 SELECT state,GROUP_CONCAT(cast(total as char)) FROM ( SELECT state,SUM(i.quantity) total FROM shops s LEFT JOIN items i ON i.shop=s.shopid WHERE state=5 GROUP by item ) s 

Answer 2

Edit:

 group_concat(CAST(quantity AS CHAR)) 

For

 SUM(quantity) 

-

 SELECT s.`state`, i.`item`, SUM(i.`quantity`) AS quantities FROM `shops` AS s LEFT JOIN `items` AS i ON i.`shop` = s.`shopid` WHERE s.`state` = 5 GROUP BY i.`item` 

Answer 3

As far as I know, you cannot do this in MySQL. Dynamic columns are only supported to the extent of group_contcat (), which still combines multiple rows of results into a single column.

Only if you have a fixed / limited number of Total X -s, can you explicitly declare them in the request as such.