Find people with at least 2 purchases of the same product in a month

If I have this data for example :

ProductName     ServiceDate   Name   StockId   Fee

LG                1/10/2011    John   1         0 
Sony              2/20/2011    Mike   2         0 
LG                1/11/2011    John   1         0
Samsung           2/21/2011    Steve  3         0 
Toshiba           3/2/2011     Kelly  4         0 
LG                5/6/2011     Sean   1         0   

now imagine there are millions of records. How do I find the people who bought 2 or more products in the same month with the sa开发者_如何学Gome stockid?

This can be done using a GROUP BY statement. The only wrinkle is you have to use MONTH and YEAR functions to convert your date to a usable format to group on.

SELECT  Name
FROM    Products
GROUP BY
        MONTH(ServiceDate)
        , YEAR(ServiceDate)
        , StockID
        , Name
HAVING  COUNT(*) >= 2        

This query should give you a list of names and stock id's where the name purchased 2 or more of the stock ids in the specified month.

SELECT Name, StockId
FROM table
WHERE YEAR(ServiceDate) = 2011 AND MONTH(ServiceDate) = 2
GROUP BY Name, StockId
HAVING COUNT(Name)> 1

Select *
FROM (
select StockID, ProductName, MONTH(serviceDate) as strMonth, year(serviceDate) as strYear COUNT(productName) as ProdCount
FROM testTable

Group By ProductName, StockID, MONTH(serviceDate), year(serviceDate)
)a  where ProdCount > 1

You can use the Below query.

create table sales 
(ProductName varchar(10),
ServiceDate date,
Name varchar(10),
StockId number(10)
);



insert into sales values('LG',to_date('10-01-2011','dd-mm-yyyy'),'John' ,1);
insert into sales values('Sony',to_date('20-02-2011','dd-mm-yyyy'),'Mike' ,2);
insert into sales values('LG',to_date('11-01-2011','dd-mm-yyyy'),'John' ,1);
insert into sales values('Samsung',to_date('21-02-2011','dd-mm-yyyy'),'Steve' ,3);
insert into sales values('Toshiba',to_date('02-03-2011','dd-mm-yyyy'),'Kelly' ,4);
insert into sales values('LG',to_date('06-05-2011','dd-mm-yyyy'),'Sean' ,1) ; 
insert into sales values('Sony',to_date('11-01-2011','dd-mm-yyyy'),'John' ,1);

Query:

 select name from sales group by to_char(servicedate,'YYYYMM'),stockid,name having count(*) > 2;

with cte as ( 
select distinct date_format(serviceDate, %Y-%m) as month , name, count(product_name)  over 
(partition by name , date_format(serviceDate, %Y-%m) ) as total_orders 
from sales 
) 

select distinct name 
from cte 
where total_orders >=2