group a multiple union statement

Question

I have looked all over the site but can't find the solution to my problem. I tried to abstract it as far as possible.

The query I have is like this:

select sup, a, b, c, d
from
(
    select sup, a, 0, 0, 0 from x where y = 1 group by sup
    union
    select sup, 0, b, 0, 0 from x where y = 2 group by sup
    union
    select sup, a, 0, c, 0 from x where y = 3 group by sup
    union
    select sup, a, 0, 0, d from x where y = 4 group by sup
)

This works fine and I get the results I expect.

BUT

What I would like is to group these results on the sup column. When I add the GROUP BY sup after this SQL it gives the following error:

[SQL0122] Column SUP or expression in SELECT-list is invalid.

Can someone please help me out how to overcome this problem?

The real SQL I use is like this:

select 
    date(tt1.datum_toegevoegd_aan_stat) as datum_toegevoegd_aan_stat, 
    tt1.sts_supplier_number as sts_supplier_number, 
    tt1.beschadigd as beschadigd, 
    tt1.incompleet as incompleet, 
    tt1.verkeer_geleverd as verkeerd_geleverd, 
    tt1.manco as manco,
    tt1.fout_besteld as fout_besteld,
    tt1.niet_besteld as niet_besteld,
    tt1.fout_magazijn as fout_magazijn
from 
(       
        select date(datum_toegevoegd_aan_stat) as datum_toegevoegd_aan_stat, sts_supplier_number, 
            count(orl_conditie_code) as beschadigd, 0 as incompleet, 0 as verkeer_geleverd, 0 as manco, 0 as fout_besteld, 0 as niet_besteld, 0 as fout_magazijn 
        from stsgopf where orl_conditie_code = '001' 
        group by sts_supplier_number, date(datum_toegevoegd_aan_stat)
    union       
        select date(datum_toegevoegd_aan_stat) as datum_toegevoegd_aan_stat, sts_supplier_number, 
            0 as beschadigd, count(orl_conditie_code) as incompleet, 0 as verkeer_geleverd, 0 as manco, 0 as fout_besteld, 0 as niet_besteld, 0 as fout_magazijn 
        from stsgopf where orl_conditie_code = '002' 
        group by sts_supplier_number, date(datum_toegevoegd_aan_stat)
    union       
        select date(datum_toegevoegd_aan_stat) as datum_toegevoegd_aan_stat, sts_supplier_number, 
            0 as beschadigd, 0 as incompleet, count(orl_conditie_code) as verkeer_geleverd, 0 as manco, 0 as fout_besteld, 0 as niet_besteld, 0 as fout_magazijn
        from stsgopf where orl_conditie_code = '003' 
        group by sts_supplier_number, date(datum_toegevoegd_aan_stat)
    union       
        select date(datum_toegevoegd_aan_stat) as datum_toegevoegd_aan_stat, sts_supplier_number, 
            0 as beschadigd, 0 as incompleet, 0 as verkeer_geleverd, count(orl_conditie_code) as manco, 0 as fout_besteld, 0 as niet_besteld, 0 as fout_magazijn
        from stsgopf where orl_conditie_code = '004' 
        group by sts_supplier_number, date(datum_toegevoegd_aan_stat)
    union       
        select date(datum_toegevoegd_aan_stat) as datum_toegevoegd_aan_stat, sts_supplier_number, 
            0 as beschadigd, 0 as incompleet, 0 as verkeer_geleverd, 0 as manco, count(orl_conditie_code) as fout_besteld, 0 as niet_besteld, 0 as fout_magazijn  
        from stsgopf where orl_conditie_code = '201' 
        group by sts_supplier_number, date(datum_toegevoegd_aan_stat)
    union       
        select date(datum_toegevoegd_aan_stat) as datum_toegevoegd_aan_stat, sts_supplier_number, 
            0 as beschadigd, 0 as incompleet, 0 as verkeer_geleverd, 0 as manco, 0 as fout_besteld, count(orl_conditie_code) as niet_besteld, 0 as fout_magazijn 
        from stsgopf where orl_conditie_code = '202' 
        group by sts_supplier_number, date(datum_toegevoegd_aan_stat)
    union       
        select date(datum_toegevoegd_aan_stat) as datum_toegevoegd_aan_stat, sts_supplier_number, 
            0 as beschadigd, 0 as incompleet, 0 as verkeer_geleverd, 0 as manco, 0 as fout_besteld, 0 as niet_besteld, count(orl_conditie_code) as fout_magazijn 
        from stsgopf where orl_conditie_code = '203' 
        group by sts_supplier_number, date(datum_toegevoegd_aan_stat)
) as tt1
where date(tt1.datum_toegevoegd_aan_stat) between '2016-09-26' and '2016-09-29' 
group by sts_supplier_number

Answer 1

What you are trying to do looks very much like a pivot operation. You could probably achieve the result you are looking for without a derived table, using conditional aggregation:

SELECT
  sts_supplier_number,
  COUNT(CASE WHEN orl_conditie_code = '001' THEN orl_conditie_code END) AS beschadigd,
  COUNT(CASE WHEN orl_conditie_code = '002' THEN orl_conditie_code END) AS incompleet,
  COUNT(CASE WHEN orl_conditie_code = '003' THEN orl_conditie_code END) AS verkeer_geleverd,
  COUNT(CASE WHEN orl_conditie_code = '004' THEN orl_conditie_code END) AS manco,
  COUNT(CASE WHEN orl_conditie_code = '201' THEN orl_conditie_code END) AS fout_besteld,
  COUNT(CASE WHEN orl_conditie_code = '202' THEN orl_conditie_code END) AS niet_besteld,
  COUNT(CASE WHEN orl_conditie_code = '203' THEN orl_conditie_code END) AS fout_magazijn
FROM
  stsgopf
WHERE
  DATE(tt1.datum_toegevoegd_aan_stat) BETWEEN '2016-09-26' AND '2016-09-29'
  AND orl_conditie_code IN ('001', '002', '003', '004', '201', '202', '203')
GROUP BY
  sts_supplier_number
;

Note that the above will give you counts per sts_supplier_number across the entire interval specified. If you want counts per sts_supplier_number and day, add DATE(tt1.datum_toegevoegd_aan_stat) to the GROUP BY and SELECT:

SELECT
  DATE(tt1.datum_toegevoegd_aan_stat) AS datum_toegevoegd_aan_stat,
  sts_supplier_number,
  COUNT(CASE WHEN orl_conditie_code = '001' THEN orl_conditie_code END) AS beschadigd,
  COUNT(CASE WHEN orl_conditie_code = '002' THEN orl_conditie_code END) AS incompleet,
  COUNT(CASE WHEN orl_conditie_code = '003' THEN orl_conditie_code END) AS verkeer_geleverd,
  COUNT(CASE WHEN orl_conditie_code = '004' THEN orl_conditie_code END) AS manco,
  COUNT(CASE WHEN orl_conditie_code = '201' THEN orl_conditie_code END) AS fout_besteld,
  COUNT(CASE WHEN orl_conditie_code = '202' THEN orl_conditie_code END) AS niet_besteld,
  COUNT(CASE WHEN orl_conditie_code = '203' THEN orl_conditie_code END) AS fout_magazijn
FROM
  stsgopf
WHERE
  DATE(tt1.datum_toegevoegd_aan_stat) BETWEEN '2016-09-26' AND '2016-09-29'
  AND orl_conditie_code IN ('001', '002', '003', '004', '201', '202', '203')
GROUP BY
  DATE(tt1.datum_toegevoegd_aan_stat),
  sts_supplier_number
;