HimanUCC HimanUCC - 5 months ago 19
SQL Question

SQL count without group by

I have a table that has user_id and purchase_id. I want to filter the table so that only users with more than 2 purchases (i.e. there are more than 2 rows for that user in the table). I used count and group by, but it does not work in a way I want.

create view myview as
select user_Id, purchase_id, count(*) as count from mytable group by user_Id;

select user_id, purchase_id from myview where count >2;


But it gives me only users (only one user_id) that has more than 2 purchase and it does not give me all of their purchases. For example if the table looks like this:

user_id purchase_id
1 1212
1 1312
2 1232
1 1321
3 1545
3 4234


My query gives me this :

1 1212
3 1545


But I want this:

1 1212
1 1312
1 1321
3 1545
3 4234

Answer

change your last sql like this

select mt.user_id, mt.purchase_id 
from myview mv 
inner join mytable mt 
on mt.user_id=mv.user_id where mv.count >5;