CODEWITHSUNDEEP
mysqlsqldatabasepivot
DaveM
DaveM

Reputation: 55

MySQL - Combining multiple selects from same table into one result table with a group by

I have a table of meter readings which holds a meterNo, readingValue and readingDate.

I want to show the total sum of readingValue per meterNo per year.

For example:

SELECT meterNo, SUM(readingValue) '2009' FROM readings
WHERE readingDate >= '2009-01-01' AND readingDate <= '2009-12-31'
GROUP BY meterNo;

Will display the table:

   meterNo  2009
   --------------
     4       50
     5       5
     12      30
     13      63
     18      18
     26      484
     27      21
     28      510
     29      28

Which is what I want, it shows the total reading value for each meter in 2009. However, I need the result table to display a column for each year. So it would be something like this:

   meterNo  2009  2010  2011  2012
   --------------------------------
     1             20    35     50
     2                   45     35
     3                   50     60
     4       50    35    20     30
     5       5     10    10     15
     6                          30

And so on...

I can reuse the where part of the query:

WHERE readingDate >= '2009-01-01' AND readingDate <= '2009-12-31'

And just change the date for each year, butt how do I display each WHERE result as its own column in the one result table?

Upvotes: 1

Views: 8364

Answers (3)

Taryn
Taryn

Reputation: 247840

MySQL does not have a PIVOT function but you can convert the rows of data into columns using an aggregate function with a CASE expression.

If you have a limited number of years, then you can hard-code the query:

select meterNo,
  sum(case when year(readingDate) = 2009 then readingValue else 0 end) `2009`,
  sum(case when year(readingDate) = 2010 then readingValue else 0 end) `2010`,
  sum(case when year(readingDate) = 2011 then readingValue else 0 end) `2011`,
  sum(case when year(readingDate) = 2012 then readingValue else 0 end) `2012`,
  sum(case when year(readingDate) = 2013 then readingValue else 0 end) `2013`
from readings
group by meterno;

See SQL Fiddle with Demo

But if you are going to have an unknown number of values or what the query to adjust as new years are added to the database, then you can use a prepared statement to generate dynamic SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN year(readingDate) = ',
      year(readingDate),
      ' THEN readingValue else 0 END) AS `',
      year(readingDate), '`'
    )
  ) INTO @sql
FROM readings;

SET @sql 
  = CONCAT('SELECT meterno, ', @sql, ' 
            from readings
            group by meterno');

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

See SQL Fiddle with Demo. Both give the result:

| METERNO | 2009 | 2010 | 2012 | 2013 | 2011 |
----------------------------------------------
|       1 |   90 |  180 |    0 |   90 |   90 |
|       2 |   50 |    0 |   90 |    0 |    0 |
|       3 |   80 |   40 |   90 |   90 |    0 |

As a side note, if you want null to display in the rows without values instead of the zeros, then you can remove the else 0 (see Demo)

Upvotes: 7

bozdoz
bozdoz

Reputation: 12870

You can use CASE WHEN to do this. It allows you to sum multiple values based on multiple conditions. In your example, you could do something like this (I've simplified my example because I don't have access to your actual data):

SELECT meterNo, 
sum(CASE WHEN readingDate = 2009 THEN readingValue END) as '2009',
sum(CASE WHEN readingDate = 2010 THEN readingValue END) as '2010',
sum(CASE WHEN readingDate = 2011 THEN readingValue END) as '2011',
sum(CASE WHEN readingDate = 2012 THEN readingValue END) as '2012'
FROM readings
GROUP BY meterNo

You need to switch up the conditions with your own--sum(CASE WHEN readingDate >= '2009-01-01' AND readingDate <= '2009-12-31' THEN readingValue END), etc--but it should work. Here's a working example that I did on sqlFiddle: http://sqlfiddle.com/#!2/6990e/29. Output is five columns with sums of each meterNo.

Upvotes: 2

jurgenreza
jurgenreza

Reputation: 6086

You could do something like this:

SELECT 
    meterNo, SUM(readingValue) AS `total`, YEAR(readingDate)
FROM 
    readings
GROUP BY 
    meterNo, YEAR(readingDate);

result:

METERNO     TOTAL   YEAR(READINGDATE)
1           90      2009
1           90      2010
2           50      2009
3           80      2009
3           40      2010

Also note how you can use YEAR function instead of the where clause you have.

SEE FIDDLE

Upvotes: 0

Related Questions