CODEWITHSUNDEEP
sqloracle-database
TonyS
TonyS

Reputation: 99

Oracle SQL - how to merge many rows into one in select

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'.

Upvotes: 0

Views: 64

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271121

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.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37493

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

Upvotes: 0

Related Questions