Can I build semi-advanced reports using only MySQL queries?

Question

I have a MySQL table whose structure and data looks something like this:

name |       date | type  | value
-----+------------+-------+------
foo  | 2013-01-01 | blue  |     4
foo  | 2013-01-02 | green |     1
foo  | 2013-01-01 | blue  |     9
foo  | 2013-01-02 | green |     5
bar  | 2013-01-01 | blue  |    10
bar  | 2013-01-02 | green |     4
bar  | 2013-01-01 | blue  |     6
bar  | 2013-01-02 | green |     2
meow | 2013-01-01 | blue  |     5
meow | 2013-01-02 | green |     6
meow | 2013-01-01 | blue  |     4
meow | 2013-01-02 | green |     4

I'm trying to construct a query that will produce this output:

name | blue | green
-----+------+------
foo  |   13 |     6
bar  |   16 |     6
meow |    9 |    10

The name column is maintained. The blue and green columns of the output are generated based on the values of the type table column. The values of those two columns are the accumulated value of the value table column for all dates. I hope that made sense.

This is a bit out of my league when it comes to MySQL so I have no idea where to start. Is this possible using MySQL, and if so, how? For instance, how does one create output columns based on the values of a single table column?

Edit: I should specify; there is no need for this to be a single query. As long as it can be done without involving any other technologies than MySQL then I have no constraints regarding subqueries, multiple queries, views etc.

score 3 · Accepted Answer · answered Sep 11 '13 at 14:45

You can use CASE and SUM() if you have known number of values.

SELECT  Name,
        SUM(CASE WHEN type = 'blue' THEN value ELSE 0 END) blue,
        SUM(CASE WHEN type = 'green' THEN value ELSE 0 END) green
FROM    tableName
GROUP   BY Name

SQLFiddle Demo

But if you have unknown number of values, you can build a dynamic query for that which yield the same result as with static query,

SELECT  GROUP_CONCAT(DISTINCT
        CONCAT('SUM(CASE WHEN type = ''',
               type,
               ''' THEN value ELSE 0 END) AS ',
               CONCAT('`', type, '`')
               )) INTO @sql
FROM TableName;

SET @sql = CONCAT('SELECT Name, ', @sql, ' 
                   FROM    tableName
                    GROUP   BY Name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQLFiddle Demo

Joachim Isaksson · Answer 2 · 2013-09-11T14:50:18.743

A simple SUM with GROUP BY should solve it for you;

SELECT name, 
  SUM(CASE WHEN type='blue' THEN value ELSE 0 END) blue,
  SUM(CASE WHEN type='green' THEN value ELSE 0 END) green
FROM Table1
GROUP BY name;

An SQLfiddle to test with.

...or using somewhat briefer MySQL specific code;

SELECT name, 
  SUM(IF(type='blue', value, 0)) blue,
  SUM(IF(type='green', value, 0)) green
FROM Table1
GROUP BY name;

Another SQLfiddle.

xlecoustillier · Answer 3 · 2013-09-11T14:52:17.077

0

Try:

SELECT name,
    SUM(CASE WHEN type = 'blue' THEN value ELSE 0 END) AS blue,
    SUM(CASE WHEN type = 'green' THEN value ELSE 0 END) AS green
FROM yourTable
GROUP BY name

There's no PIVOT function in MySQL like there is in SQL Server for instance.

edited Sep 11 '13 at 14:52

answered Sep 11 '13 at 14:45

xlecoustillier

16,183
14
60
85

score 0 · Answer 4 · answered Sep 11 '13 at 14:45

0

The output format is making this (unneccessary?) hard for you. A query that would give an output like this:

name | color | amount

which in your code you could easily rewrite to

name | blue | green

would that work for you?

SELECT name, color, sum(value) as amount
FROM tablename
GROUP BY name, color

answered Sep 11 '13 at 14:45

Joost Pastoor

253
1
7

1

That's the whole point of the question, IMHO. "build semi-advanced reports" – xlecoustillier Sep 11 '13 at 14:47

score 0 · Answer 5 · answered Sep 11 '13 at 14:46

0

try

SELECT name, sum(CASE type WHEN 'blue' THEN value END) blue,
             sum(CASE type WHEN 'green' THEN value END) green FROM tbl
GROUP BY name

answered Sep 11 '13 at 14:46

Carsten Massmann

26,510
2
22
43

score 0 · Answer 6 · edited May 23 '17 at 12:11

0

What you want to do is not possible in a single SQL statement from within MySQL. What you can do is create a "meta-query" which generates an sql statement which you can then prepare and execute.

Have a look at, e.g., mysql select dynamic row values as column names, another column as value

edited May 23 '17 at 12:11

Community

1
1

answered Sep 11 '13 at 14:56

Stefan Winkler

3,871
1
18
35

There's no need for this to be a single statement. Check the updated question. – Hubro Sep 11 '13 at 16:00

Can I build semi-advanced reports using only MySQL queries?

6 Answers6