Question revised
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 seem to get group_concat to grab the total column in result #1
Thanks in advance
Change:
group_concat(CAST(quantity AS CHAR))
To
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`