按月分组并保留产品名称

Question

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.

Answer 1

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 |

http://sqlfiddle.com/#!9/a52a66/5