0

I'm trying to count occurrences of values in a specific column.

cus_id      prod_id     income 
100           10          90
100           10          80
100           20         110
122           20           9
122           30          10

When doing the query, I would like to receive something like this:

cus_id     count(prod_id = 10)    (prod_id = 20)    (prod_id = 30)     sum(income)
100                2                     1                 0                280
122                0                     1                 1                 19

At the moment my initial approach is this:

select cus_id, prod_id, count(prod_id), sum(income) from t group by 1,2

Any insights would be highly appreciated. Thanks in advance!

hjun
  • 73
  • 7
  • Use `case` _expressions_ to do _conditional aggregation_. – jarlh Oct 06 '22 at 08:46
  • But what if there's so many prod_ids as in from 1-100? Thatll be too much manual work – hjun Oct 06 '22 at 08:48
  • Which SQL engine are you using? Some system has PIVOT functionality, which helps you automate this type of work. – Kota Mori Oct 06 '22 at 08:50
  • Your initial approach is fine and sufficient. Just do the rest in your app, using your programming language and a loop to fill the grid as you desire. I'd add an `ORDER BY` clause to your query, though, to get all products for a customer in a block. – Thorsten Kettner Oct 06 '22 at 08:50
  • Try to use SUM or COUNT with OVER (PARTITION BY...). – Ricardo Rodrigues Oct 06 '22 at 08:53
  • you can use `group by cus_id` - not specifying particular id's – MagicKriss Oct 06 '22 at 08:55
  • Does this answer your question? [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) – astentx Oct 06 '22 at 08:57

1 Answers1

0

Oracle SQL

with t (cus_id, prod_id, income) as (
select 100, 10,   90 from dual union all
select 100, 10,   80 from dual union all
select 100, 20,  110 from dual union all
select 122, 20,    9 from dual union all
select 122, 30,   10 from dual)
select
cus_id,
count(case when prod_id = 10  then income end) sum_prod_10,
count(case when prod_id = 20  then income end) sum_prod_20,
count(case when prod_id = 30  then income end) sum_prod_30,
count(income)                                  sum_income
from t
group by cus_id;

    CUS_ID SUM_PROD_10 SUM_PROD_20 SUM_PROD_30 SUM_INCOME
---------- ----------- ----------- ----------- ----------
       122           0           1           1          2
       100           2           1           0          3

SQL>

https://dbfiddle.uk/XZs56Hks

Amir Kadyrov
  • 1,253
  • 4
  • 9