0

I can't find a way to divide the result of my 2 queries.

They look like that :

SELECT periode, cee_ref_no, SUM(somme) AS total FROM V_STAT_NAMUR 
WHERE code_ref_no IN (1, 2, 3, 4, 5, 6, 193, 215, 237, 259, 281)
AND periode BETWEEN '201401' AND '201412'
AND cee_ref_no = '961'
GROUP BY periode, cee_ref_no
ORDER BY periode;

AND

SELECT periode, cee_ref_no, SUM(somme) AS total FROM V_STAT_NAMUR
WHERE code_ref_no IN (7, 8, 9, 10, 205, 227, 249, 271, 293)
AND periode BETWEEN '201401' AND '201412'
AND cee_ref_no = '961'
GROUP BY periode, cee_ref_no
ORDER BY periode;

They look pretty similar, and both return something like this :

 DATE  | CEE_REF_NO | TOTAL
201401 |   961      | 10713
201402 |   961      | 9593
...    |   961      | ...
201412 |   961      | 10426

How can I merge these to obtain something like this :

 DATE  | CEE_REF_NO | TOTAL
201401 |   961      | Total Q1/ Total Q2
201402 |   961      | Total Q1/ Total Q2
...    |   961      | ...
201412 |   961      | Total Q1/ Total Q2

Everything I tried returned either only one row, or 12 rows with the same result.

Thanks a lot !

Kris Rice
  • 3,300
  • 15
  • 33
Playlist
  • 37
  • 7

3 Answers3

0

Use conditional aggregation:

SELECT periode, cee_ref_no,
       SUM(CASE WHEN code_ref_no IN (1, 2, 3, 4, 5, 6, 193, 215, 237, 259, 281) THEN somme ELSE 0 END) AS total_1,
       SUM(CASE WHEN code_ref_no IN (7, 8, 9, 10, 205, 227, 249, 271, 293) THEN somme ELSE 0 END) AS total_2,
       (SUM(CASE WHEN code_ref_no IN (1, 2, 3, 4, 5, 6, 193, 215, 237, 259, 281) THEN somme ELSE 0 END) /
        SUM(CASE WHEN code_ref_no IN (7, 8, 9, 10, 205, 227, 249, 271, 293) THEN sommeEND)
       ) AS ratio
FROM V_STAT_NAMUR 
WHERE  AND
      periode BETWEEN '201401' AND '201412'
GROUP BY periode, cee_ref_no
ORDER BY periode;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You can try with this:

select q1.periode, q1.cee_ref_no, q1.total as Total1, q2.total as Total2, q1.total/q2.total as Division
from (
    SELECT periode, cee_ref_no, SUM(somme) AS total FROM V_STAT_NAMUR 
    WHERE code_ref_no IN (1, 2, 3, 4, 5, 6, 193, 215, 237, 259, 281)
    AND periode BETWEEN '201401' AND '201412'
    AND cee_ref_no = '961'
    GROUP BY periode, cee_ref_no
  ) q1
  join (
    SELECT periode, cee_ref_no, SUM(somme) AS total FROM V_STAT_NAMUR
    WHERE code_ref_no IN (7, 8, 9, 10, 205, 227, 249, 271, 293)
    AND periode BETWEEN '201401' AND '201412'
    AND cee_ref_no = '961'
    GROUP BY periode, cee_ref_no
  ) q2
  on q1.Periode=q2.Periode
 and q1.cee_ref_no=q2.cee_ref_no

Basicaly you have just to create 2 subselect with your queries and join them by period and cee_ref_no (in case you were going to include more than 1 cee_ref_no, in your example there is just one). Then you will be able to divide the Total from q1 and q2.

Take care about the join, I don't know if your data will have information for all the months in both queries.

PS: Query not tested, written directly on editor.

James
  • 2,954
  • 2
  • 12
  • 25
0

Try this

    Select periode, cee_ref_no, total1/total2 as Total
    From (
    SELECT periode, cee_ref_no, 
    sum(Case when code_ref_no in (1, 2, 3, 4, 5, 6, 193, 215, 237, 259, 281) Then somme else 0 end) as total1,
    sum(Case when code_ref_no in (7, 8, 9, 10, 205, 227, 249, 271, 293) Then somme else 0 end) as total2
    From my_table
WHERE code_ref_no IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 193, 205, 215, 227, 237, 249, 259, 271, 281, 293)
    AND periode BETWEEN '201401' AND '201412'
    AND cee_ref_no = '961'
    GROUP BY periode, cee_ref_no
    )
    ORDER BY periode;
Gaj
  • 888
  • 5
  • 5