multiple counts

Question

I have this table :

user product log_date 

1    aaa     02-02-2020
2    aaa     05-01-2020
1    aaa     03-02-2020
2    aaa     06-01-2020
3    ccc     09-08-2020

i want to query this

product total_entries  total_users
aaa     4              2
ccc     1              1

i did this :

SELECT 
 product,
 count(*) total_entries,
 count(*) over(partition by product,user ) total_users
from table 
group by product; 

i get this error

ORA-00979: not a GROUP BY expression

any help ?

Answer 1

with t(userid, product, log_date ) as (
select 1,    'aaa',     '02-02-2020' from dual union all
select 2,    'aaa',     '05-01-2020' from dual union all
select 1,    'aaa',     '03-02-2020' from dual union all
select 2,    'aaa',     '06-01-2020' from dual union all
select 3,    'ccc',     '09-08-2020' from dual
)
select product, count(*) total_entries, count(distinct userid) total_users
from t
group by product;

PRODUCT TOTAL_ENTRIES TOTAL_USERS
------- ------------- -----------
ccc                 1           1
aaa                 4           2

Answer 2

Try this instead:

SELECT 
product,
count(*) OVER (partition by product) total_entries,
count(*) over(partition by product,user ) total_users
from table;