CODEWITHSUNDEEP
mysqlsqlsum
Matts
Matts

Reputation: 55

Can I substract in SQL a GROUP BY SUM from another GROUP BY SUM and return a GROUP BY result?

I have a SQL table similar to this:

date totalVal CTC CTC2
2020-01-01 100 Mr1 Sup1
2020-03-01 200 Mr1 Sup1
2020-01-01 300 Mr2 Sup1
2019-01-01 50 Mr1 Sup1
2019-03-01 150 Mr1 Sup1
2019-01-01 125 Mr2 Sup1
2019-01-01 100 Mr2 Sup2

I'm looking for a single query that would return me the of sum of totalVal (year 2020) minus sum of totalVal (year 2019) , per CTC and per month.

I've managed so far to get the sum GROUP BY CTC, MONTH (date) and this works fine, but can't manage to go further on.

$query="
SELECT CTC
     , MONTH(date) as monthVal
     , sum(totalVal) as quarterlySum
  FROM $table 
 WHERE YEAR(date)=2020 
   AND CTC2 = 'SUP1' 
 GROUP 
    BY CTC
     , MONTH(date)
";

I'm looking to get a return in the likes of

Mr1;1;-150
Mr1;3;50
Mr2;1;75

Is it possible to get this from a single query?

Thanks,

Upvotes: 1

Views: 50

Answers (1)

forpas
forpas

Reputation: 164139

With conditional aggregation:

SELECT CTC, 
       MONTH(date) AS monthVal,
       SUM(CASE WHEN YEAR(date) = 2020 THEN totalVal END) -
       SUM(CASE WHEN YEAR(date) = 2019 THEN totalVal END)
FROM $table 
WHERE YEAR(date) IN (2019, 2020) AND CTC2='SUP1'
GROUP BY CTC, monthVal

or:

SELECT CTC, 
       MONTH(date) AS monthVal,
       SUM(totalVal * CASE YEAR(date) WHEN 2020 THEN 1 WHEN 2019 THEN -1 END)
FROM $table 
WHERE YEAR(date) IN (2019, 2020) AND CTC2='SUP1'
GROUP BY CTC, monthVal

Upvotes: 3

Related Questions