Nested SQL Queries throws Error while they run in distinct form in MySql

Question

I have a SELECT Query which should have several nested SELECT Queries from the same table, but different WHERE clauses to produce new columns.

The problem is that each sub query in its single form runs good, but when they turn into sub-queries, MySql throws an ERROR.

Here is My SQL :

SELECT user, 
(SELECT SUM(amount) from my_table WHERE type='form1' group by user) as form1,
(SELECT SUM(amount) from my_table WHERE type='form2' group by user) as form2,
(SELECT SUM(amount) from my_table WHERE type='form3' group by user) as form3,
(SELECT SUM(amount) from my_table WHERE type='form4' group by user) as form4,
(SELECT SUM(amount) from my_table WHERE type='form5' group by user) as form5,
(SELECT SUM(amount) from my_table WHERE type='form6' group by user) as form6
from my_table group by user;

I want the Query to produce this structure:

user | form1 | form2 | form3 | form4 | form5 | form6
     |       |       |       |       |       |

How should I edit this SQL?

You have an extra comma after as form6.

Barmar
– Barmar

2014-12-08 18:20:01 +00:00
Commented Dec 8, 2014 at 18:20 — Barmar
– Barmar, Commented Dec 8, 2014 at 18:20
@Barmar, its just a typo in stackoverflow :)

Ramin Omrani
– Ramin Omrani

2014-12-08 18:29:40 +00:00
Commented Dec 8, 2014 at 18:29 — Ramin Omrani
– Ramin Omrani, Commented Dec 8, 2014 at 18:29
Then why haven't you corrected it?

Barmar
– Barmar

2014-12-08 20:32:53 +00:00
Commented Dec 8, 2014 at 20:32 — Barmar
– Barmar, Commented Dec 8, 2014 at 20:32

Gordon Linoff · Accepted Answer · 2014-12-08 18:19:07Z

5

You want conditional aggregation:

select user,
       sum(case when type = 'form1' then amount end) as form1,
       sum(case when type = 'form2' then amount end) as form2,
       sum(case when type = 'form3' then amount end) as form3,
       sum(case when type = 'form4' then amount end) as form4,
       sum(case when type = 'form5' then amount end) as form5,
       sum(case when type = 'form6' then amount end) as form6
from my_table
group by user;

Your error is occurring at least because the subqueries are returning more than one row. The group by in the subqueries pretty much means that they will return more than one row for each user.

answered Dec 8, 2014 at 18:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

PlantTheIdea Over a year ago

upvoted as this is correct, however for efficiency sake you may consider (a) making the CASE value rather than search, and (b) adding an ELSE 0 to each.

Collectives™ on Stack Overflow

Nested SQL Queries throws Error while they run in distinct form in MySql

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