0

From the following query:

SELECT id , typ_produktu , miary_wewnetrzne , CASE WHEN symbol LIKE '760%' THEN SUM(sal-type_number) ELSE NULL END sum_760 , CASE WHEN symbol LIKE '860%' THEN SUM(sal-type_number) ELSE NULL END sum_860 FROM 537_MV WHERE rep_date = 20180930 AND id = 'DP\1601' AND miary_wewnetrzne IN ('WN', 'wynik') AND typ_w IN( 'RT_SZT', 'brak') GROUP BY id , symbol , typ_produktu , miary_wewnetrzne , typ_w

I get this table:

id typ_produktu miary_wewnetrzne sum760 sum860 ------- ------------ ---------------- ------ ------ DP\1601 brak wynik 17 0 DP\1601 brak wynik 9 0 DP\1601 brak wynik 1 0 DP\1601 brak wynik 0 1 DP\1601 AIN WN 0 0

How can I write my query to get only one record in table for ID (i.e. DP\1601), if typ_produktu = 'brak', I'd like to replace it with any other for this ID in the example it will be 'AIN'.

Improve this question

2 Answers 2

Reset to default
1

You can use aggregation:

SELECT id, COALESCE(MAX(CASE WHEN typ_produktu <> 'BRAK' THEN typ_produktu END), MAX(typ_produktu)) as typ_produktu MAX(miary_wewnetrzne) SUM(CASE WHEN symbol LIKE '760%' THEN sal - type_number END) as sum_760 SUM(CASE WHEN symbol LIKE '860%' THEN sal - type_number END) as sum_860 FROM 537_MV WHERE rep_date = 20180930 AND id = 'DP\1601' AND miary_wewnetrzne IN ('WN', 'wynik') AND typ_w IN ( 'RT_SZT', 'brak') GROUP BY id;

Note that if you want one row per id, then it should be the only column in the GROUP BY.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Use conditional aggregation and remove typ_w column from group by

SELECT id , typ_produktu , miary_wewnetrzne , sum(CASE WHEN symbol LIKE '760%' THEN sal-type_number ELSE 0 END) as sum_760 , sum(CASE WHEN symbol LIKE '860%' THEN sal-type_number ELSE 0 END) as sum_860 FROM 537_MV WHERE rep_date = 20180930 AND id = 'DP\1601' AND miary_wewnetrzne IN ('WN', 'wynik') AND typ_w IN( 'RT_SZT', 'brak') GROUP BY id, typ_produktu, miary_wewnetrzne
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.