how to calculate year and it value?

Question

I have got some result for this query but instead of it i want to change a result like below

Result to be shown::

    2011                     2012
    26267.620000000003       404603.20999999996
    144384.36                173245.96000000002 
    199704.47999999998       208524.42

Query

SELECT
    YEAR (PAYMENTDATE) AS YEARNUMBER,
    MONTH (PAYMENTDATE) AS MONNUMBER,
    SUM (AMOUNT) AS MONTOTAL
    FROM 
    PAYMENTS
    GROUP BY 
    YEAR (PAYMENTDATE)

Result:: for above query

2011        26267.620000000003  
2011        144384.36   
2012        173245.96000000002  
2012        208524.42

You need pivot as, for example, here: http://stackoverflow.com/questions/7674786/mysql-pivot-table — Andrius Naruševičius, Jan 27 '14 at 07:13

Saharsh Shah · Answer 1 · 2014-01-27T07:33:27.020

0

Try this:

SELECT MONNUMBER, 
       MAX(CASE WHEN YEARNUMBER = 2011 THEN MONTOTAL ELSE 0 END) AS Year2011,
       MAX(CASE WHEN YEARNUMBER = 2012 THEN MONTOTAL ELSE 0 END) AS Year2011
FROM (SELECT YEAR(PAYMENTDATE) AS YEARNUMBER, MONTH(PAYMENTDATE) AS MONNUMBER, 
             SUM(AMOUNT) AS MONTOTAL
      FROM PAYMENTS
      GROUP BY YEAR(PAYMENTDATE)
    ) AS A
GROUP BY MONNUMBER

edited Jan 27 '14 at 07:33

answered Jan 27 '14 at 07:15

Saharsh Shah

28,687
8
48
83

SQL error #1:Syntax error: Encountered "2011" it showing some error like this?? – jagan Jan 27 '14 at 07:21
Again same error SQL error #1:Syntax error: Encountered "\'2011\'" at line 2, column 69 – jagan Jan 27 '14 at 07:27
k k i wil try to solve?? – jagan Jan 27 '14 at 07:37

how to calculate year and it value?

1 Answers1