group a multiple union statement

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_aa

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
;

StackExchange Database Administrators Q#151686, answer score: 9