Error with MySQL Subquery

Question

I have a MySQL query to produce a view which displays two columns, the two columns are sub query's but for some reason I am getting error.

SELECT 
(SUM(cards_required) FROM `orders` WHERE statuses = 1) As Count,
(SUM(cards_required) FROM `orders` WHERE statuses = 2) As Count
FROM `orders` WHERE user_id = 1

error message

#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 'FROM orders WHERE statuses = 1) As Count, (SUM(cards_required) FROM orders ' at line 3

#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 'FROM orders WHERE statuses = 1) As Count, (SUM(cards_required) FROM orders ' at line 3 — Dev.W
– Dev.W, Commented Sep 28, 2015 at 14:03
ok youve got multiple where clauses and from clauses - why ? — amdixon
– amdixon, Commented Sep 28, 2015 at 14:04

Jim · Accepted Answer · 2015-09-28 14:06:00Z

1

The syntax you're using isn't quite right. You can use IF to conditionally sum:

SELECT 
SUM(IF(statuses = 1,cards_required,0)) As status_1,
SUM(IF(statuses = 2,cards_required,0)) As status_2
FROM `orders` WHERE user_id = 1

answered Sep 28, 2015 at 14:06

Jim

22.7k6 gold badges55 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dev.W Over a year ago

Wow that worked! I usually work in SQL seems there are more differences than I thought!

Collectives™ on Stack Overflow

Error with MySQL Subquery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related