5

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
9

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
;
  • Thank you very much, thisis indeed exactly what I was looking for. And so much easier than the way that I was thinking myself :-) – Ruben van Straten Oct 10 '16 at 4:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.