CODEWITHSUNDEEP
mysqldatabase
Paul
Paul

Reputation: 257

How to query SQL-database how many times an item has been purchased between two dates?

I'm a beginner when it comes to databases and this question makes my brain melt. I have a database with the standard columns you would imagine: Id, price, description etc. How should I organize the database so that it's possible to query the database how many times an item has been purchased between 2012-01-08 and 2012-03-23?

Upvotes: 0

Views: 1041

Answers (3)

Citizentools
Citizentools

Reputation: 132

Concur with the other answers. 

SELECT itemcode, count(itemcode) 'times purchased', sum(quantity) 'amount purchased'  
FROM `temp_trans` 
WHERE `trans_date` >= '2012-01-08' AND `trans_date` <= '2012-03-23' 
GROUP BY itemcode

Here's a live example: http://sqlfiddle.com/#!2/13def/1

Upvotes: 2

Rich Andrews
Rich Andrews

Reputation: 4188

select id, count(*)
from mytable
where date between '2012-01-01' and '2012-02-02'
group by id

This gives you the number of times each item was purchased. The field you group by will depend on how you want your result to be split up, using id or a unique name would work depending on what you want to do with the result.

The between clause is inclusive btw

Edit: good point on the comment! Probably should have said "use an appropriate field to group on, like the name or id of the item." in some of our systems description and name are used interchangeably :)

Upvotes: 1

Marco
Marco

Reputation: 57593

If you have a date column called dt you can use

SELECT COUNT(Id) FROM your_table
WHERE dt BETWEEN 2012-01-08 AND 2012-03-23
  AND Id = your_item_id

Upvotes: 1

Related Questions