CODEWITHSUNDEEP
sqlsql-server
Arpit Tripathi
Arpit Tripathi

Reputation: 55

How to formulate this SQL Query

I have a table Retail

CustomerID       Itemset
    1              31
    1              30 
    1              78
    2              31
    2              91
    3              30
    3              31

I want to to find the count of CustomerID having both 30 & 31 in Itemset. I.e. In this case 1 & 3, so the count is 2.

I tried formulating the query using the & operator but the query isn't returning the right answer.

my query- Select count(customerID) from Retail where Itemset=30 & 31

Upvotes: 0

Views: 76

Answers (4)

A_Sk
A_Sk

Reputation: 4630

try this..

select CustomerID,count(*)  from [dbo].[Retail] where Itemset in(30,31)
group by CustomerID

If you want only the count result then use Common Table Expression

like this:

;with cte1
as
(select CustomerID,count(*) as rs  from [dbo].[Retail] where Itemset in(30,31)
    group by CustomerID
)
select rs from cte1

Upvotes: 0

Danimal111
Danimal111

Reputation: 2062

You only want to count the distinct CustomerID's.... I haven't tested this, but believe this is the way to go...

Select count( distinct customerID)
from Retail
where Itemset in (30,31)

--- This was wrong... after a little tinkering, I came to the already provided solution... please disregard....

 Select count(*)
 from (
       Select CustomerID
       from Retail
       where Itemset in (30,31)
       group by CustomerID
       having count(CustomerID = 2)
  ) T

--- Kudos. This assumes that CustomerID and Itemset cannot have duplicates. In other words, it assumes you wouldn't have two or more rows with 1,30 for CustomerID and Itemset.

Upvotes: 0

Davide Lorenzo MARINO
Davide Lorenzo MARINO

Reputation: 26926

Select count(distinct(CustomerID))  from (
    select r30.CustomerID
    from Retail r30
    inner join Retail r31 on
    r31.CustomerID = r30.CustomerID
    where 
    r30.Itemset = 30 
    and r31.Itemset = 31
) T

Upvotes: 2

ASh
ASh

Reputation: 35646

select count(*)
from
(
    select CustomerId
    from Retail
    where Itemset in (30,31)
    group by CustomerId
    having count(distinct Itemset) = 2   -- this guarantees that CustomerId have at least one 30 and at least one 31 in column Itemset 
) T

Upvotes: 3

Related Questions