SQL query with multiple SELECT

Question

I have table to present in my app, and what i want is to present table, in which one row have values that calculated by following:

SELECT SUM (val) FROM report_orbiting_vals rov 
  WHERE rov.orbiting_group_type_id = ? AND rov.orbiting_group_indice_id = ?

For first row i want to present 4 values, each one will look like that:

SELECT SUM (val) FROM report_orbiting_vals rov 
  WHERE rov.orbiting_group_type_id = (1,2,3,4) AND rov.orbiting_group_indice_id = 1

Where (1,2,3,4) are 4 different SELECT statements with (1,2,3,4) values correspondingly. In next row i will change rov.orbiting_group_indice_id = 2 and want to use same rov.orbiting_group_type_id values (1,2,3,4).

Im new at SQL and i want to ask, how to present table with that valeus?
So it should be something like column1-value, column2-value, column3-value, column4-value.

Thanks!

Updated: I want something like following:

SELECT 
    (SELECT
        SUM(val)
    FROM
        report_orbiting_vals rov
    WHERE
        rov.orbiting_group_type_id = 1
    AND rov.orbiting_group_indice_id = 1) as colOne,
(SELECT
        SUM(val)
    FROM
        report_orbiting_vals rov
    WHERE
        rov.orbiting_group_type_id = 1
    AND rov.orbiting_group_indice_id = 2) as colTwo,
(SELECT
        SUM(val)
    FROM
        report_orbiting_vals rov
    WHERE
        rov.orbiting_group_type_id = 1
    AND rov.orbiting_group_indice_id = 3) as colThree,
(SELECT
        SUM(val)
    FROM
        report_orbiting_vals rov
    WHERE
        rov.orbiting_group_type_id = 1
    AND rov.orbiting_group_indice_id = 4) as colFourth

Unfortunately, above code not work, it produce an error, but i hope you understand what i want for now.

Updated (2):

I tried 2 solutions provided below, first one:

SELECT
(SELECT
 SUM(val)
 FROM
 report_orbitings rov
 WHERE
 rov.orbiting_group_type_id = 1
 AND rov.orbiting_group_indice_id = 1) as colOne,
(SELECT
 SUM(val)
 FROM
 report_orbitings rov
 WHERE
 rov.orbiting_group_type_id = 1
 AND rov.orbiting_group_indice_id = 2) as colTwo,
(SELECT
 SUM(val)
 FROM
 report_orbitings rov
 WHERE
 rov.orbiting_group_type_id = 1
 AND rov.orbiting_group_indice_id = 3) as colThree,
(SELECT
 SUM(val)
 FROM
 report_orbitings rov
 WHERE
 rov.orbiting_group_type_id = 1
 AND rov.orbiting_group_indice_id = 4) as colFourth
FROM report_orbitings rv

Output:

While second:

SELECT SUM(CASE WHEN rov.orbiting_group_type_id = 1 THEN val ELSE 0 END) as type1_sum,
SUM(CASE WHEN rov.orbiting_group_type_id = 2 THEN val ELSE 0 END) as type2_sum,
SUM(CASE WHEN rov.orbiting_group_type_id = 3 THEN val ELSE 0 END) as type3_sum,
SUM(CASE WHEN rov.orbiting_group_type_id = 4 THEN val ELSE 0 END) as type4_sum
FROM report_orbitings rov
WHERE rov.orbiting_group_type_id in (1,2,3,4)
AND rov.orbiting_group_indice_id = 1

Output:

To be clear i want to admit, that my data base don't have data yet, just structure. Still, i suppose second code work while first does not, because it output [Null] instead of nothing. Why is there difference in those 2 examples? It should produce identical output.

sagi · Accepted Answer · 2016-09-12 07:28:33Z

4

Not sure I followed, either you want the total sum of them, then you need to use IN() :

SELECT SUM (val) FROM report_orbiting_vals rov
WHERE rov.orbiting_group_type_id in (1,2,3,4) 
  AND rov.orbiting_group_indice_id = 1

Or you want the sum of each one in a different column :

SELECT SUM(CASE WHEN rov.orbiting_group_type_id = 1 THEN val ELSE 0 END) as type1_sum,
       SUM(CASE WHEN rov.orbiting_group_type_id = 2 THEN val ELSE 0 END) as type2_sum,
       SUM(CASE WHEN rov.orbiting_group_type_id = 3 THEN val ELSE 0 END) as type3_sum,
       SUM(CASE WHEN rov.orbiting_group_type_id = 4 THEN val ELSE 0 END) as type4_sum
FROM report_orbiting_vals rov
WHERE rov.orbiting_group_type_id in (1,2,3,4) 
  AND rov.orbiting_group_indice_id = 1

answered Sep 12, 2016 at 7:28

sagi

40.6k7 gold badges66 silver badges87 bronze badges

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

7 Comments

Evgeniy Kleban Over a year ago

thank you, i many not make myself clear, i want to each select from your question will be SELECT SUM (val) FROM report_orbiting_vals rov WHERE rov.orbiting_group_type_id = AND rov.orbiting_group_indice_id = 1, next one SELECT SUM (val) FROM report_orbiting_vals rov WHERE rov.orbiting_group_type_id = 2 AND rov.orbiting_group_indice_id = 1, next one 3, last 4.

sagi Over a year ago

@EvgeniyKleban That's what my query does, it will return the result in each column .

Evgeniy Kleban Over a year ago

thank you, but could you explain meaning of WHERE rov.orbiting_group_type_id in (1,2,3,4)? Isnt it neccesarry when we explicity set those values in queries?

sagi Over a year ago

That's just for performance , you can remove that part. I used it to tell the optimizer to scan only id 1,2,3,4 . @EvgeniyKleban

Evgeniy Kleban Over a year ago

would you be so kind to look at my updated answer and tell me, why your answer is work in compare to other one? Or maybe both works?

|

i486 · Accepted Answer · 2016-09-12 07:46:47Z

1

Maybe you need this:

SELECT 
  (SELECT SUM(val) FROM report_orbiting_vals rov
   WHERE rov.orbiting_group_type_id = rv.orbiting_group_type_id
   AND rov.orbiting_group_indice_id = 1) as colOne,
  (SELECT SUM(val) FROM report_orbiting_vals rov
   WHERE rov.orbiting_group_type_id = rv.orbiting_group_type_id
   AND rov.orbiting_group_indice_id = 2) as colTwo,
  (SELECT SUM(val) FROM report_orbiting_vals rov
   WHERE rov.orbiting_group_type_id = rv.orbiting_group_type_id
   AND rov.orbiting_group_indice_id = 3) as colThree,
  (SELECT SUM(val) FROM report_orbiting_vals rov
   WHERE rov.orbiting_group_type_id = rv.orbiting_group_type_id
  AND rov.orbiting_group_indice_id = 4) as colFourth,
  rv.orbiting_group_type_id
FROM report_orbiting_vals rv

answered Sep 12, 2016 at 7:46

i486

6,5354 gold badges26 silver badges46 bronze badges

4 Comments

Evgeniy Kleban Over a year ago

thank you, your asnwer is correct, but could you please explain why my updated code not working? I guess it is identical to your solution

i486 Over a year ago

See the last row in my query - your updated code does not have second FROM report_orbiting_vals.

sagi Over a year ago

Wow that's 4 unnecessary selects!

i486 Over a year ago

@sagi We don't know the DB structure - I hope SUM(val) will include more than 1 row.

Collectives™ on Stack Overflow

SQL query with multiple SELECT

2 Answers 2

7 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related