something like "group by" for columns?

I have table like this:

+----+---------+---------+--------+
| id | value_x | created | amount |
+----+---------+---------+--------+

value_x is set of six strings, lets say "one", "two", "three", etc. I need to create report like this:

+--------------+-------------------------+-------------------+----------------------+
| day_of_month |          "one"          |      "two"        |          [etc.]      |
+--------------+-------------------------+-------------------+----------------------+
|  01-01-2011  | "sum(amount) where value_x = colum name" for this specific day     |
+--------------+-------------------------+-------------------+----------------------+

Most obvious solu开发者_运维问答tion is:

SELECT SUM(amount), DATE(created) FROM `table_name` WHERE value_x=$some_variable GROUP BY DATE(created)

And loop this query six times with another value for $some_variable in every iteration, but I'm courious if is it possible to do this in single query?

What you're asking is called a "pivot table" and is typically achieved as below. The idea is for each potential value of value_x you either produce a 1 or 0 per row and sum 1's and 0's to get the sum for each value.

SELECT
  DATE(created),
  SUM(CASE WHEN value_x = 'one' THEN SUM(amount) ELSE 0 END) AS 'one',
  SUM(CASE WHEN value_x = 'one' THEN SUM(amount) ELSE 0 END) AS 'two',
  SUM(CASE WHEN value_x = 'one' THEN SUM(amount) ELSE 0 END) AS 'three',
  etc...
FROM table_name
GROUP BY YEAR(created), MONTH(created), DAY(created)

This will come close:

SELECT
   s.day_of_month
   ,GROUP_CONCAT(CONCAT(s.value_x,':',s.amount) ORDER BY s.value_x ASC) as output
   FROM (
         SELECT DATE(created) as day_of_month
                ,value_x
                ,SUM(amount) as amount
         FROM table1
         GROUP BY day_of_month, value_x
         ) s
GROUP BY s.day_of_month

You will need to read the output and look for the value_x prior to the : to place the items in the proper column.

The benefit of this approach over @Michael's approach is that you do not need to know the possible values of field value_x beforehand.