Get the Sum of Two Table GROUP BY date in year

Question

I'm making a donation report, I have two tables tbldonation and tblpubdonation I want to get their sum group by year.

tbldonation:
amount      |      received
100         :      2016-01-02 08:42:20
100         :      2015-12-01 09:20:00

tblpubdonation:
amount      |      received
100         :      2015-12-22 09:20:00

My query is :

SELECT * from
(
    (SELECT YEAR(received) as YY, sum(amount) as AMT FROM tbldonation)
    UNION ALL
    (SELECT YEAR(received) as YY, sum(amount) as AMT FROM tblpubdonation)
) results
WHERE results.YY <= Curdate()
GROUP BY results.YY
ORDER BY results.YY DESC

I'm getting a result but it's not accurate. It should be

YY        |       AMT
2016      :       100
2015      :       200

But my result is:

YY        |       AMT
2016      :       200
2015      :       100

The value is misplaced.

score 0 · Answer 1 · answered Jan 11 '16 at 08:12

You are using sum(amount) in the query .So in tbldonation the sum becomes .In pbldonationsum(amount)=100.At the end the order is given by years .So 2016 is at first and 2015 at the end.Year 2015 wont get the amount value 200 because union operator is used and both are having 2015 as common .So this is to chabged in the data itself as per me or else some data glitch.If I am wrong please suggest me also so that I can improve myself. Thank you. Regards, Raju.

score 0 · Accepted Answer · edited May 23 '17 at 11:53

0

Try following for MySql (Since MySQL lacks support for FULL JOIN. we have to simulate It)

    SELECT  Yr , SUM(amountSum)
    FROM    ( SELECT    SUM(ISNULL(t1.amount, 0)) AS amountSum ,
                t1.year AS Yr
      FROM      tbldonation t1
      GROUP BY  t1.YEAR
      UNION ALL
      SELECT    SUM(ISNULL(t2.amount, 0)) AS amountSum ,
                t2.year AS Yr
      FROM      tblpubdonation t2
      GROUP BY  t2.YEAR
    ) Tbl
   GROUP BY Tbl.Yr

Following works for T-SQL

SELECT SUM (ISNULL(t1.amount,0)) + SUM(ISNULL(t2.amount,0)), ISNULL(t1.YEAR,t2.YEAR)
FROM dbo.tbldonation t1 
FULL OUTER JOIN dbo.tblpubdonation t2 ON t1.YEAR = t2.YEAR
GROUP BY t1.YEAR, t2.YEAR

edited May 23 '17 at 11:53

Community

1
1

answered Jan 11 '16 at 08:15

tchelidze

8,050
1
29
49

@SeeMore okey , what is output you get ? – tchelidze Jan 11 '16 at 08:32
Incorrect parameter count in the call to native function 'ISNULL' and also what is FULL OUTER JOIN? t1.YEAR will not be equal to t2.YEAR I think. – Al bassam Jan 11 '16 at 08:34
I think the problem in my query is the order. But I can't get it. – Al bassam Jan 11 '16 at 08:36
@SeeMore really ? `ISNULL` takes 2 argument. I modified answer several minutes ago, copy and paste newer version of code above. – tchelidze Jan 11 '16 at 08:36
The result is the same Incorrect Parameter. Did you try it? – Al bassam Jan 11 '16 at 08:40
@SeeMore Sorry, my bad, it works for `Sql Sever` only, since `mysql `has does not have `Full Join` – tchelidze Jan 11 '16 at 09:00
Ohh okay. Did you have a solution for without using Full Join? – Al bassam Jan 11 '16 at 09:08
@SeeMore I am trying [MySQL FULL JOIN](http://stackoverflow.com/questions/7978663/mysql-full-join) alternative, but query becomes more complicated. – tchelidze Jan 11 '16 at 09:11
It worked with a little modification. I erased ISNULL. It's getting error. Thank you sir. – Al bassam Jan 11 '16 at 11:29

Get the Sum of Two Table GROUP BY date in year

2 Answers2