CODEWITHSUNDEEP
sqloracle-database
user9741285
user9741285

Reputation:

Count one column by considering another column

This is my Audit table:

id      productnr    invoicenr         price              
1         2            1000             20
2         2            1000             20
3         31           1000             25
4         23           1001             15
5         23           1002             15
6         71           1002             27
7         2            1003             20

I need to write 2 different queries and I have no idea how should I write them:

  1. How many invoices do have ONLY product 2 (+ Total price)?

  2. How many invoices do have diffrent products but product 2 as well (+ Total price)?

the result I want looks like this:

Count    totalPrice
1250       206030

Upvotes: 0

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

If I understand correctly, you can use two levels of aggregation:

select sum(case when num_products = 1 then 1 else 0 end) as has_2_only,
       sum(case when num_products = 1 then total else 0 end) as has_2_total,
       count(*) as has_2,
       sum(total) as has_2_total
from (select invoicenr, sum(price) as total,
             count(distinct product_nr) as num_products,
             max(case when product = 2 then 1 else 0 end) as has_product_2
      from t
      group by invoicenr
     ) i
where has_product_2 = 1

Upvotes: 2

Related Questions