CODEWITHSUNDEEP
sqlsql-server
AhmetEmre90
AhmetEmre90

Reputation: 501

How to Find the Last Week's Most Sold Books in SQL

I'm trying to build an online bookstore for my homework.
I want to list the most sold books by time (last week, last month, all time).
I wrote the all time query but couldn't figure the last week query.
Can someone please help me?

Here is the table: 

TbSoldBooks (saleID, BookID, SaleDate)

(I searched but can't find the answer)

Update: This is for all time most sold books. 

SELECT b.bookID, COUNT(b.bookID) AS All-Time  
FROM TbSoldBooks b  
GROUP BY b.bookID

Upvotes: 0

Views: 2525

Answers (2)

Val Bakhtin
Val Bakhtin

Reputation: 1464

SELECT top 10 b.bookID, COUNT(b.bookID) 
FROM TbSoldBooks b  
GROUP BY b.bookID
Where SaleDate >= DateAdd(day, -7, getDate()) -- last week
Order by 2 desc

SELECT top 10 b.bookID, COUNT(b.bookID) 
FROM TbSoldBooks b  
GROUP BY b.bookID
Where SaleDate >= DateAdd(month, -1, getDate()) -- last month
Order by 2 desc

Upvotes: 1

zmbq
zmbq

Reputation: 39023

Well, I don't want to ruin all your homework for you, but you could try to GROUP by BookID and to COUNT the sales for each book.

EDIT: I see you're confused about comparing dates. Take a look at DATEADD, then.

Upvotes: 1

Related Questions