CODEWITHSUNDEEP
mysqlsql
Webdeveloper_Jelle
Webdeveloper_Jelle

Reputation: 2856

Get data from 2 years into one result row

I have this query:

SELECT BC_PID, Year, Period, 
CASE WHEN Year = 2017 THEN SUM(Allin+Basis+Entry+IFNULL(3CXUsers, 0)) END as '2017', 
CASE WHEN Year = 2018 THEN SUM(Allin+Basis+Entry+IFNULL(3CXUsers, 0)) END as '2018'  
FROM ext_partnertotals WHERE
(Year = '2018' OR Year = '2017') AND Period = 6
GROUP BY BC_PID, Year

The result is this:
enter image description here

Now I want to have the 2 rows for one BC_PID into one row.
So that I have the numbers for 2017 and 2018 for each BC_PID on one row.
I currently have the totals on 2 rows.

It should give:

  BC_PID |Period|2017 |2018
   2.001 |6     |1.744|2.444
 100.003 |6     |0    |0

Check this SQLfiddle: http://sqlfiddle.com/#!9/4c9b5/1

Upvotes: 0

Views: 34

Answers (2)

cf_en
cf_en

Reputation: 1661

Tweaked your fiddle slightly:

SELECT BC_PID, Period,
SUM(CASE WHEN Year = 2017 THEN Allin+Basis+Entry+IFNULL(3CXUsers, 0) ELSE 0 END) as '2017',
SUM(CASE WHEN Year = 2018 THEN Allin+Basis+Entry+IFNULL(3CXUsers, 0) ELSE 0 END) as '2018'
FROM ext_partnertotals
WHERE (Year = '2018' OR Year = '2017') AND Period = 6
GROUP BY BC_PID, Period

Upvotes: 1

kayakpim
kayakpim

Reputation: 995

SELECT BC_PID, Year, Period, 
CASE WHEN Year IN (2017,2018) 
   THEN SUM(Allin+Basis+Entry+IFNULL(3CXUsers, 0)) END as '2017_8'
FROM ext_partnertotals 
WHERE (Year = '2018' OR Year = '2017') 
AND Period = 6 
GROUP BY BC_PID, Year

Upvotes: 0

Related Questions