Evgeniy Kleban Evgeniy Kleban - 2 months ago 7
SQL Question

SQL query with multiple SELECT

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:

enter image description here

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:

enter image description here

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.

Answer

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