CODEWITHSUNDEEP
mysql
Grant
Grant

Reputation: 2441

MySQL subquery syntax error for cumulative totals

I have a table as follows:

test_id | id | option | visits
------------------------------
1       | 1  | a      | 7
1       | 2  | b      | 12
1       | 3  | a      | 2
1       | 4  | b      | 4

I need to get the cummulative totals of visits for option 'a' and option 'b'.

My query is as follows:

SELECT *,
    ( SUM(visits) WHERE `option` = 'a' ) as visitsA, 
    ( SUM(visits) WHERE `option` = 'b' ) as visitsB
    FROM `table`
    WHERE test_id = 1

I get the following error:

#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 'WHERE `option` = 'a' ) as visitsA, ( SUM(visits) WHERE `option` = 'b' ' at line 2

Upvotes: 0

Views: 53

Answers (1)

Alma Do
Alma Do

Reputation: 37365

You're doing it wrong. Such "WHERE" isn't allowed in SUM(). Instead, do:

SELECT *,
    SUM(IF(`option`='a', visits, 0)) as visitsA, 
    SUM(IF(`option`='b', visits, 0)) as visitsB
FROM `table`
WHERE test_id = 1

Upvotes: 3

Related Questions