How to write a querry sum when condition

[mecctro]

I have a table like this


Product type sold
beer hotel 20
beer hotel 30
beer inn 20
beer inn 40
coke b&b 20
coke hotel 40
coke hotel 10


and want to get the result like


product hotel inn b&b
beer 50 60 0
coke 50 0 20


How to write a query (Oracle)

Thanks

 

[LKBrwnDBA]

Search for: "rows to columns", "pivot table" and/or "crosstab"




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Mecctro, can you please post what you have tried so far, along with the results your code generates?

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[mecctro]

There are several problems with that. I read company's base on my work, but they are not happy with me using it so I cant ask for advice. I don't have access to internet from work so I can't be looking for solutions from there. I did search forums, but my English and knowledge of SQL waren't good enough to find the answer.

Anyway, would this work (I can't try it till tomorrow)?

SELECT product, CASE(WHEN type= 'hotel' THEN sum(sold) ELSE 0 END,
CASE(WHEN type= 'inn' THEN sum(sold) ELSE 0 END,
CASE(WHEN type= 'b & b' THEN sum(sold) ELSE 0 END
FROM sales

 

[SantaMufasa]

Mecctro,

There are several ways to code your solution. Here is one way:

set define off

select * from sales;

PRODUCT      TYPE                  SOLD
------------ --------------- ----------
beer         hotel                   20
beer         hotel                   30
beer         inn                     20
beer         inn                     40
coke         b&b                     20
coke         hotel                   40
coke         hotel                   10

7 rows selected.

select product, nvl(sum(hotel),0) hotel, nvl(sum(inn),0) inn, nvl(sum("B&B"),0) "B&B"
  from (select product, sold hotel, null inn, null "B&B" from sales where type = 'hotel'
        union
        select product, null, sold,null from sales where type = 'inn'
        union
        select product, null, null, sold from sales where type = 'b&b')
 group by product
/

PRODUCT           HOTEL        INN        B&B
------------ ---------- ---------- ----------
beer                 50         60          0
coke                 50          0         20

The "set define off" command is a SQL*Plus command that causes the "&" to not be interpretted as a prompt character.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[mecctro]

Thanks, I've been at work, tried this and it worked.

select product, SUM(CASE WHEN type = 'hotel' THEN sold ELSE 0 END)
SUM(CASE WHEN type = 'b&b' THEN sold ELSE 0 END)
SUM(CASE WHEN type = 'inn' THEN sold ELSE 0 END)

from sales group by product

 

[Dagon]

If that helped, you can acknowledge the fact by clicking on the "Thank SantaMufasa for this valuable post!" button.