0

Need help on a query to sum all values by month grouped by comma

Trying:

SELECT GROUP_CONCAT( (
    SELECT SUM( price ) 
    FROM sales
    GROUP BY MONTH( date ) )
    )
FROM sales
LIMIT 0 , 30

but returning: #1242 - Subquery returns more than 1 row

group_concat alone won't help cause I need the sum of the values by month.

Any help? Thanks

Henrique
  • 509
  • 7
  • 25

1 Answers1

2

Untested:

SELECT GROUP_CONCAT(a.totprice) 
FROM (
  SELECT SUM(price) as totprice
  FROM sales
  GROUP BY MONTH(date)
) a;

Note that you will get max 12 values, since any different years are merged. You probably want to add an ORDER BY in the subquery to get the months in a defined order in the result.

Erik Ekman
  • 2,051
  • 12
  • 13
  • Thanks, but returning a strange value: [BLOB - 13 B] – Henrique Mar 01 '12 at 22:13
  • OK, MySQL thinks the result is binary for some reason. What is the data type of the price column? Change first row to `SELECT CAST(GROUP_CONCAT(a.totprice) as CHAR)` and you will get text. – Erik Ekman Mar 01 '12 at 22:18
  • Thanks, worked perfectly. It was float and tried varchar as well, but your solution worked! Thanks – Henrique Mar 01 '12 at 22:20
  • Great. You will probably not need the `CAST` in the normal query, phpmyadmin seems to create the blob problem: http://stackoverflow.com/questions/2133936/using-group-concat-in-phpmyadmin-will-show-the-result-as-blob-3b – Erik Ekman Mar 01 '12 at 22:22