CODEWITHSUNDEEP
phpmysql
user3589022
user3589022

Reputation: 11

How to SELECT on the basis of date from different table

I have two tables - sales and sale_items

sales has - id and date

sale_items has - sale_id, product_id, product name and quantity

now, when I do a sale, a record enters in 'sales' and 'sale_items'. In sale_items, in the sale_id column, it mentions the id of the sale in which it was put.

I constructed the query by which I can get the quantity of the products sold from the starting of time.

What i want to get is, the quantity of a product sold on a particular date. How do I do that?

Query I am using for getting the total quantity : 

SELECT si.product_id, SUM( si.quantity ) soldQty 
from sale_items si group by si.product_id

Upvotes: 0

Views: 84

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

You can try as below by joining the tables.

select
si.product_id,
SUM( si.quantity ) soldQty 
from 
sale_items si
inner join sales s on s.id = si.sale_id
where s.date = 'your date'
group by si.product_id

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

Just join your sales table and add a where clause

SELECT si.product_id,
SUM( si.quantity ) soldQty 
FROM sale_items si
JOIN sales s ON(s.id=si.sale_id)
WHERE s.date='2014-04-30'
GROUP BY si.product_id

Upvotes: 1

Related Questions