CODEWITHSUNDEEP
sqloracle-databasegroup-by
ycf
ycf

Reputation: 57

Oracle query group by type

I have an output from my query:

Item   Type   Qty
 1       A     2
 2       A     3
 3       B     1
 4       B     2
 5       C     1
 6       D     3

Type to be grouped: A, B

I need my output to look like this:(after sum the qty and group by)

Type      Qty
  A        5
  B        3
OTHERS     4

when the Type is not defined, it will group in 'OTHERS'. Is this possible to be done using analytic function or do I need to create my own function for this?

Upvotes: 3

Views: 426

Answers (2)

Sai
Sai

Reputation: 689

  select type,sum(qty) from (select decode(type,c,'OTHERS',d,'OTHERS') type,qty from your_table) group by type;

                       (or)

  select type,sum(qty) from (select (case when type in ('A','B') then type else 'others' end) type,qty from your_table) group by type;

Upvotes: 0

Rachcha
Rachcha

Reputation: 8816

Assuming your table / view name is x, this gives exactly the desired output:

SELECT CASE WHEN type IN ('A', 'B') THEN type ELSE 'OTHERS' END AS type,
       SUM(qty) AS qty
  FROM x
GROUP BY CASE WHEN type IN ('A', 'B') THEN type ELSE 'OTHERS' END
ORDER BY 1

Upvotes: 2

Related Questions