How to output two select queries in a stored procedure?

Question

I have two queries that I am trying to put into a stored procedure to use in a SSRS report. I looked into using UNION but I think you need same number of columns for that. On first query, I am finding the total number of values for each country in the reference table.

Second query is the sum of all values in the columns for all the countries(total count of the values). I need this to be just one row as it is the grand total so there is no need for multiple rows. The reason I am trying to put these both into one stored procedure it to output one cursor that has all the fields I need for the report. Does anyone have any suggestions?

My table COLORS_PER_COUNTRY has columns like RED_CODE , BLUE_CODE ,NA_CODE_1 , PURPLE_CODE , GREEN_CODE , YELLOW_CODE , NA_CODE_2.

These all are VARCHAR2(2) and should stored values like 'RD','NA','PK'.

SELECT
    M.CDE_COUNTRY || '-' || M.DESC_COUNTRY COUNTRY,
    SUM(CASE WHEN RED_CODE = 'RD' THEN 1 ELSE 0 END),
    SUM(CASE WHEN BLUE_CODE = 'BE' THEN 1 ELSE 0 END),
    SUM(CASE WHEN NA_CODE_1 = 'NA' THEN 1 ELSE 0 END),
    SUM(CASE WHEN PURPLE_CODE = 'PE' THEN 1 ELSE 0 END),
    SUM(CASE WHEN GREEN_CODE = 'GN' THEN 1 ELSE 0 END),
    SUM(CASE WHEN YELLOW_CODE = 'YW' THEN 1 ELSE 0 END),
    SUM(CASE WHEN NA_CODE_2 = 'NA' THEN 1 ELSE 0 END)
FROM 
    MASTER.T_COUNTRY M,
    PERSON.T_PERSON P,
    PERSON.T_CASE C
WHERE 
    TABLE_NAME IN ('H1')
    C.PERSON_ID = P.PERSON_ID
    M.CDE_COUNTRY = SUBSTR(LPAD(P.PERSON_ID,6,0)1,2)
GROUP BY 
    M.CDE_COUNTRY, M.DESC_COUNTRY COUNTRY;

Second query:

SELECT 
    SUM(CASE WHEN RED_CODE = 'R' THEN 1 ELSE 0 END),
    SUM(CASE WHEN BLUE_CODE = 'B' THEN 1 ELSE 0 END),
    SUM(CASE WHEN NA_CODE_1 = 'NA' THEN 1 ELSE 0 END),
    SUM(CASE WHEN PURPLE_CODE = 'P' THEN 1 ELSE 0 END),
    SUM(CASE WHEN GREEN_CODE = 'G' THEN 1 ELSE 0 END),
    SUM(CASE WHEN YELLOW_CODE = 'Y' THEN 1 ELSE 0 END),
    SUM(CASE WHEN NA_CODE_2 = 'NA' THEN 1 ELSE 0 END)
FROM 
    PERSON.T_PERSON P,
    PERSON.T_CASE C
WHERE 
    C.PERSON_ID = P.PERSON_ID;

Example:

Countries	Red	Blue
Total	5	10
Albania	1	3
Wales	2	4
Germany	2	3

Answer 1

One of the ways to do it could be creating a cte defining all the countries (like '01-Germany') and one more named 'Total' - this would be row labels in final resultset. Join that cte to your colors table and do conditional aggregation grouped by label.

WITH    --  S a m p l e    D a t a :
  colors_per_country ( CDE_COUNTRY, COUNTRY_DESC, 
                       RED_CODE, BLUE_CODE, NA_CODE_1, PURPLE_CODE, 
                       GREEN_CODE, YELLOW_CODE, NA_CODE_2 ) AS
    ( Select '01', 'Germany', 'RD',  'BE',    Null,    Null,  Null, Null, 'NA' 
      From Dual UNION ALL
      Select '01', 'Germany', 'RD',  'BE',    Null,    Null, Null, Null, 'NA' 
     From Dual UNION ALL
     Select '01', 'Germany', Null,  'BE',    Null,    Null, 'GR', Null, Null 
     From Dual UNION ALL
     --
     Select '02', 'Albania', Null,  'BE',    Null,    Null,  'GR', 'YW', Null 
     From Dual UNION ALL
     Select '02', 'Albania', 'RD',  'BE',    Null,    Null, Null, Null, Null 
     From Dual UNION ALL
     Select '02', 'Albania', Null,  'BE',    Null,    Null, Null,  Null, Null 
     From Dual UNION ALL
     --
     Select '03', 'Wales', 'RD',  'BE',    Null,    Null, Null,  Null, Null 
     From Dual UNION ALL
     Select '03', 'Wales', Null,  'BE',    Null,    Null, Null,  Null, Null 
     From Dual UNION ALL
     Select '03', 'Wales', 'RD',  'BE',    Null,    Null, Null,  Null, Null 
     From Dual UNION ALL
     Select '03', 'Wales', Null,  'BE',    Null,    Null, Null,  Null, Null 
     From Dual 
   ), 

... here is the cte that defines country labels plus 'Total' label. I used colors table's CDE_COUNTRY and COUNTRY_DESC columns (read the NOTE below). You should probably use your table defining countries. The goal is to get 1 row per country plus 'Total' as a separate row (UNION ALL) ...

  labels ( CDE_COUNTRY, LBL ) AS 
      ( Select Distinct CDE_COUNTRY, CDE_COUNTRY || '-' || COUNTRY_DESC as LBL 
        From   colors_per_country 
       UNION ALL
        Select '00', 'Total' From Dual
      )

... now join the cte to your table and do the conditional aggregation, group the data by LBL and handle the order by ...

--      M a i n    S Q L :
Select    l.LBL, 
          Sum( Case When c.RED_CODE = 'RD' Then 1 Else 0 End ) as red,
          Sum( Case When c.BLUE_CODE = 'BE' Then 1 Else 0 End ) as blue, 
          Sum( Case When c.NA_CODE_1 = 'NA' Then 1 Else 0 End ) as na_1, 
          Sum( Case When c.PURPLE_CODE = 'PE' Then 1 Else 0 End ) as purple, 
          Sum( Case When c.GREEN_CODE = 'GR' Then 1 Else 0 End ) as green, 
          Sum( Case When c.YELLOW_CODE = 'YW' Then 1 Else 0 End ) as yellow, 
          Sum( Case When c.NA_CODE_2 = 'NA' Then 1 Else 0 End ) as na_2
From      colors_per_country c
Inner Join labels l ON( l.CDE_COUNTRY = c.CDE_COUNTRY Or l.CDE_COUNTRY = '00' )
Group By  l.LBL
Order By  Case When l.LBL = 'Total' Then '00' Else l.LBL End

/*      R e s u l t :
LBL                RED    BLUE     NA_1  PURPLE   GREEN  YELLOW     NA_2
--------------  ------  ------  -------  ------  ------  ------  -------
Total                5      10        0       0       2       1        2
01-Germany           2       3        0       0       1       0        2
02-Albania           1       3        0       0       1       1        0
03-Wales             2       4        0       0       0       0        0      */

See the fiddle here.

NOTE:
I put the country names in the same table as colors - you should, most probably, join country data to the colors table before joining it all to the labels cte. So, you should adjust the code to your context. There were no sample data nor description of all the tables involved to write a complete code that you could just copy - paste and run.
And, please, do not use list of tables in From clause (it's been deprecated for 30+ years now) - use JOIN syntax instead.

Answer 2

I see two ways to do it. Either you prepend an empty field into your second query's select clause, like

    SELECT 

    '',
    SUM(CASE WHEN RED_CODE = 'R' THEN 1 ELSE 0 END ),
    SUM(CASE WHEN BLUE_CODE = 'B' THEN 1 ELSE 0 END ),
    SUM(CASE WHEN NA_CODE_1 = 'NA' THEN 1 ELSE 0 END ),
    
    SUM(CASE WHEN PURPLE_CODE = 'P' THEN 1 ELSE 0 END ),
    SUM(CASE WHEN GREEN_CODE = 'G' THEN 1 ELSE 0 END ),
    SUM(CASE WHEN YELLOW_CODE = 'Y' THEN 1 ELSE 0 END ),
    SUM(CASE WHEN NA_CODE_2 = 'NA' THEN 1 ELSE 0 END )
    <other clauses here>

or you could join your two queries, like

SELECT <fields of first query here>, <fields of second query here>
FROM (first query) t1
JOIN (second query) t2
ON 1=1

of course, specify t1. and t2. for fields that would be duplicate, alias the fields in subquery, etc.

The first approach would union the results into two records, the first record representing the results of the first query and the second record representing the results of the second query.

The second approach will have a single record as a result, where you can find the fields of t1 and t2.

EDIT

This is how you can do it with a join:

SELECT t1.mytext,
       t1.sum1, t1.sum2, t1.sum3, t1.sum4, t1.sum5, t1.sum6, t1.sum7,
       t2.sum1, t2.sum2, t2.sum3, t2.sum4, t2.sum5, t2.sum6, t2.sum7
FROM (
SELECT
    M.CDE_COUNTRY || '-' || M.DESC_COUNTRY COUNTRY AS mytext,
    SUM(CASE WHEN RED_CODE = 'RD' THEN 1 ELSE 0 END) AS sum1,
    SUM(CASE WHEN BLUE_CODE = 'BE' THEN 1 ELSE 0 END) AS sum2,
    SUM(CASE WHEN NA_CODE_1 = 'NA' THEN 1 ELSE 0 END) AS sum3,
    SUM(CASE WHEN PURPLE_CODE = 'PE' THEN 1 ELSE 0 END) AS sum4,
    SUM(CASE WHEN GREEN_CODE = 'GN' THEN 1 ELSE 0 END) AS sum5,
    SUM(CASE WHEN YELLOW_CODE = 'YW' THEN 1 ELSE 0 END) AS sum6,
    SUM(CASE WHEN NA_CODE_2 = 'NA' THEN 1 ELSE 0 END) AS sum7
FROM 
    MASTER.T_COUNTRY M,
    PERSON.T_PERSON P,
    PERSON.T_CASE C
WHERE 
    TABLE_NAME IN ('H1')
    C.PERSON_ID = P.PERSON_ID
    M.CDE_COUNTRY = SUBSTR(LPAD(P.PERSON_ID,6,0)1,2)
GROUP BY 
    M.CDE_COUNTRY, M.DESC_COUNTRY COUNTRY
) t1
JOIN (
SELECT 
    SUM(CASE WHEN RED_CODE = 'R' THEN 1 ELSE 0 END) AS sum1,
    SUM(CASE WHEN BLUE_CODE = 'B' THEN 1 ELSE 0 END) AS sum2,
    SUM(CASE WHEN NA_CODE_1 = 'NA' THEN 1 ELSE 0 END) AS sum3,
    SUM(CASE WHEN PURPLE_CODE = 'P' THEN 1 ELSE 0 END) AS sum4,
    SUM(CASE WHEN GREEN_CODE = 'G' THEN 1 ELSE 0 END) AS sum5,
    SUM(CASE WHEN YELLOW_CODE = 'Y' THEN 1 ELSE 0 END) AS sum6,
    SUM(CASE WHEN NA_CODE_2 = 'NA' THEN 1 ELSE 0 END) AS sum7
FROM 
    PERSON.T_PERSON P,
    PERSON.T_CASE C
WHERE 
    C.PERSON_ID = P.PERSON_ID
) t2
ON 1=1;

and this is how you can do it with a UNION:

SELECT mytext,
       sum1, sum2, sum3, sum4, sum5, sum6, sum7
FROM (
SELECT
    M.CDE_COUNTRY || '-' || M.DESC_COUNTRY COUNTRY AS mytext,
    SUM(CASE WHEN RED_CODE = 'RD' THEN 1 ELSE 0 END) AS sum1,
    SUM(CASE WHEN BLUE_CODE = 'BE' THEN 1 ELSE 0 END) AS sum2,
    SUM(CASE WHEN NA_CODE_1 = 'NA' THEN 1 ELSE 0 END) AS sum3,
    SUM(CASE WHEN PURPLE_CODE = 'PE' THEN 1 ELSE 0 END) AS sum4,
    SUM(CASE WHEN GREEN_CODE = 'GN' THEN 1 ELSE 0 END) AS sum5,
    SUM(CASE WHEN YELLOW_CODE = 'YW' THEN 1 ELSE 0 END) AS sum6,
    SUM(CASE WHEN NA_CODE_2 = 'NA' THEN 1 ELSE 0 END) AS sum7
FROM 
    MASTER.T_COUNTRY M,
    PERSON.T_PERSON P,
    PERSON.T_CASE C
WHERE 
    TABLE_NAME IN ('H1')
    C.PERSON_ID = P.PERSON_ID
    M.CDE_COUNTRY = SUBSTR(LPAD(P.PERSON_ID,6,0)1,2)
GROUP BY 
    M.CDE_COUNTRY, M.DESC_COUNTRY COUNTRY
) t1
UNION (
SELECT 
    '' AS mytext,
    SUM(CASE WHEN RED_CODE = 'R' THEN 1 ELSE 0 END) AS sum1,
    SUM(CASE WHEN BLUE_CODE = 'B' THEN 1 ELSE 0 END) AS sum2,
    SUM(CASE WHEN NA_CODE_1 = 'NA' THEN 1 ELSE 0 END) AS sum3,
    SUM(CASE WHEN PURPLE_CODE = 'P' THEN 1 ELSE 0 END) AS sum4,
    SUM(CASE WHEN GREEN_CODE = 'G' THEN 1 ELSE 0 END) AS sum5,
    SUM(CASE WHEN YELLOW_CODE = 'Y' THEN 1 ELSE 0 END) AS sum6,
    SUM(CASE WHEN NA_CODE_2 = 'NA' THEN 1 ELSE 0 END) AS sum7
FROM 
    PERSON.T_PERSON P,
    PERSON.T_CASE C
WHERE 
    C.PERSON_ID = P.PERSON_ID
) t2;