CODEWITHSUNDEEP
phpsqlpivot
Ni Yao
Ni Yao

Reputation: 40

My SQL pivot query is returning an error though there doesnt seem to be one

My SQL query is:

SELECT * FROM 
(SELECT * FROM transactions) AS T1
PIVOT(SUM(amount) FOR month IN([March], [April], [May])) AS pvt

My Table is as follows: 

id  payee       amount  month    
1   Tom         90      March
3   Tom         66      April
4   Tom         89      May
10  Jasmine     125     April
11  Nancy       151     March
12  Jasmine     175     April
13  Nancy       152     April

My desired output is: 

payee       March  April   May
Tom         90     66      89
Jasmine     --     300     --
Nancy       151    152     --

I am running this query in a phpMyAdmin for my website the Error presented is as follows:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PIVOT(SUM(amount) FOR month IN([March], [April], [May])) AS pvt

Upvotes: 0

Views: 87

Answers (1)

Dario
Dario

Reputation: 2723

There is no PIVOT in MySQL, no CROSSTAB, no similar syntax. You have to build pivot tables by hand. A common technique is called conditional aggregation, and can be used this way:

SELECT `payee`
    , SUM(CASE WHEN `month` = 'March' THEN `amount` END) AS `March`
    , SUM(CASE WHEN `month` = 'April' THEN `amount` END) AS `April`
    , SUM(CASE WHEN `month` = 'May' THEN `amount` END) AS `May` 
  FROM `transactions`
  GROUP BY `payee`;

GROUP BY is crucial, because it restricts the scope of the aggregate function SUM. It is applied before calculation and it says there has to be only one output record per value of payee. Then I could write SUM(amount) to compute the sum of all amounts for that value of payee (ordinary aggregation) but I insert another condition SUM(CASE WHEN ... THEN ... END) and I do conditional aggregation.

Using SUM without GROUP BY would compute the sum across the whole table.

Upvotes: 1

Related Questions