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