CODEWITHSUNDEEP
sql
nubela
nubela

Reputation: 17284

SQL Query: find customers who purchased more than one book in a given year

This is the DB Schema:

Books (bookid, title, author, year)
Customers (customerid, name, email)
Purchases (customerid, bookid, year)
Reviews (customerid, bookid, rating)
Pricing (bookid, format, price)

How do I find customers (show their names and email addresses) who purchased more than one book in year 2003?

Upvotes: 0

Views: 1754

Answers (3)

JosephStyons
JosephStyons

Reputation: 58685

One more alternative:

select
  cu.name
 ,cu.email
 ,count(*) as books_purchased
from
  customers cu
 ,purchases pu
where cu.customerid = pu.customerid
  and pu.year = 2003
group by
  cu.name
 ,cu.email
having
  count(*) > 1

Upvotes: 0

soulmerge
soulmerge

Reputation: 75704

SELECT name, email, COUNT(p.customerId) as purchases_in_2003
FROM Customers c
INNER JOIN Purchases p ON c.customerId = p.customerId
WHERE p.year = 2003
GROUP BY name, email
HAVING purchases_in_2003 > 1

Upvotes: 5

Charles Bretana
Charles Bretana

Reputation: 146409

pretty much exactly like your english language question phrased it... just translated into SQL ...

Select * From Customers C
Where (Select Count(*) From Purchases
       Where customerid = C.customerid
           And Year = 2003) > 1

Upvotes: 2

Related Questions