How to optimize SQL query with multiple SELECTs from the same table?

Question

How can I optimizate this SQL Query?

(SELECT SUM (same_field) FROM same_table WHERE other_same_field = '0') AS a,
(SELECT SUM (same_field) FROM same_table WHERE other_same_field = '1') AS b,
(SELECT SUM (same_field) FROM same_table WHERE other_same_field = '2') AS c

Conrad Frix · Accepted Answer · 2013-05-07 15:49:36Z

7

When you want to do a pivot in MySQL you can do Aggregate on a Case statement

SELECT 
    SUM ( CASE WHEN other_same_field  = 0 THEN same_field ELSE 0 END) as A,
    SUM ( CASE WHEN other_same_field  = 1 THEN same_field ELSE 0 END) as B,
    SUM ( CASE WHEN other_same_field  = 2 THEN same_field ELSE 0 END) as C
FROM same_table
WHERE other_same_field IN (0, 1, 2)

answered May 7, 2013 at 15:49

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

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

3 Comments

cawecoy Over a year ago

Which one is actually better in performance, yours or @FreshPrinceOfSO's query?

Conrad Frix Over a year ago

@cawecoy I would expect the perf to be about the same, but they yield different results (1 row, 3 columns) vs (3 rows, 2 columns). The deciding factor would be how you intend to use the results. If you can I would go with FreshPrinceOfSO's since the SQL is cleaner. But if you need it in the format of 3 columns and you can't/or don't want to do the projection on the client then you'd need to go with SUM(CASE..

cawecoy Over a year ago

Thank you ConradFrix and @FreshPrinceOfSO.

Kermit · Accepted Answer · 2013-05-07 15:47:19Z

4

This will give you a vertical result.

SELECT other_same_field, SUM(same_field)
FROM same_table
WHERE other_same_field IN (0, 1, 2)
GROUP BY other_same_field

answered May 7, 2013 at 15:47

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

2 Comments

Conrad Frix Over a year ago

The wouldn't match the output of the original statement.

Kermit Over a year ago

I still think it's a valid answer on how to optimize the query.

Collectives™ on Stack Overflow

How to optimize SQL query with multiple SELECTs from the same table?

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related