I'm not sure if it's possible just with one query, but here is my problem:
SELECT
SUM(p.amount) AS sales,
AVG(p.amount) AS avg,
COUNT(p.product_id) AS total,
pd.title
FROM
payments AS p
LEFT JOIN products AS pd
ON pd.id = p.product_id
WHERE p.status = 1
GROUP BY MONTH(p.created);
The returned result is:
Array
(
[0] => stdClass Object
(
[sales] => 979.90
[avg] => 97.990000
[total] => 10
[title] => Product 1
)
[1] => stdClass Object
(
[sales] => 1139.84
[avg] => 71.240000
[total] => 16
[title] => Product 1
)
[2] => stdClass Object
(
[sales] => 789.89
[avg] => 71.808182
[total] => 11
[title] => Product 1
)
[3] => stdClass Object
(
[sales] => 739.87
[avg] => 56.913077
[total] => 13
[title] => Product 1
)
[4] => stdClass Object
(
[sales] => 569.85
[avg] => 37.990000
[total] => 15
[title] => Product 1
)
[5] => stdClass Object
(
[sales] => 999.78
[avg] => 45.444545
[total] => 22
[title] => Product 1
)
[6] => stdClass Object
(
[sales] => 569.91
[avg] => 63.323333
[total] => 9
[title] => Product 1
)
[7] => stdClass Object
(
[sales] => 199.96
[avg] => 49.990000
[total] => 4
[title] => Product 1
)
)
Since everything is grouped by month, all the names of the products are the same.
Is there any way around this? I need this to render a jQuery Flot chart, so it needs to be grouped by month. But I want to use product titles as chart labels.
If you want to group by month but have a column that list all the products title, you could use GROUP_CONCAT like this
SELECT
MONTH(p.created) AS month,
GROUP_CONCAT(DISTINCT(pd.title) SEPARATOR ', ') AS products,
SUM(p.amount) AS sales,
AVG(p.amount) AS avg,
COUNT(p.product_id) AS total
FROM
payments AS p
LEFT JOIN products AS pd
ON pd.id = p.product_id
WHERE p.status = 1
GROUP BY MONTH(p.created);
This will give you a result like this
| month | products | sales | avg | total |
|-------|---------------------------------|-------|--------|-------|
| 3 | product 2, product 3, product 1 | 10 | 2.5 | 4 |
| 4 | product 4, product 1 | 7 | 2.3333 | 3 |
| 5 | product 2, product 4, product 1 | 10 | 2.5 | 4 |
| 6 | product 2, product 3 | 6 | 2 | 3 |