CODEWITHSUNDEEP
mysqlsqlselect
TroniPM
TroniPM

Reputation: 329

sql select issue (mysql)

I tried some examples,and a lot of queries but i couldn't get this working. I have this scheme:

TABLE order
ID |...| WHEN
1  |...|2016-12-14
2  |...|2016-12-14

TABLE item
ID|...|NAME
1 |...|Pen
2 |...|Pencil
3 |...|Knife

TABLE order_itens
ID |...|amount|ID_order|ID_item|
1  |...|5     |1       |1
2  |...|2     |1       |2
3  |...|1     |1       |3
4  |...|10    |2       |1
5  |...|2     |2       |2
6  |...|5     |2       |3

ID_item is FK of TABLE.item.id; ID_order is FK of TABLE.order.id;

I need to get all registries with 'order.when<= (some custom date)', and SUM the amount of each item (Pen, Pencil, Knife, etc). Like, in december month (2016-12-01 to 2016-12-31), i have in the database this registries:

count | NAME
15    | Pen
4     | Pencil
6     | Knife

How can i accomplished this?

Upvotes: 1

Views: 76

Answers (4)

TroniPM
TroniPM

Reputation: 329

I could get this working with this query: 

SELECT SUM(b.amount) as count,c.name FROM `order` a, `order_itens` b, `item` c WHERE a.when>= MYDATEHERE AND b.id_order=a.id AND b.id_item=c.id GROUP BY c.id;

Upvotes: 1

user6935989
user6935989

Reputation:

Here you go:

SELECT SUM(order_items.amount) AS "count", item.NAME
FROM item, `order`, order_item
WHERE order_item.ID_item = item.ID
AND `order`.ID = order_item.ID_order
AND (`order`.when BETWEEN 'start_date' AND 'end_date')
GROUP BY item.ID;

Upvotes: 1

jophab
jophab

Reputation: 5509

select sum(order_items.amount),item.name
from `order_items`
inner join `item`
on order_items.id_item = item.id
inner join `order`
on order.id = order_items.id_order
where order.when between 'start_date' and 'end_date'
group by item.name

Join three tables. Group by item name. Give your start date and end date.

Upvotes: 1

Ahmad.Tr
Ahmad.Tr

Reputation: 756

This is quick answer that shall do what i understood from the question, havent tried it but iam pretty sure that it will work , just check the field names

Select sum(order_items.amount),item.name
From order_items
Inner join item
On order_items.id_item = item.id
Inner join order
On order.id = order_items.id_order
Where order.when = yourdate
Group by item.name

Upvotes: 1

Related Questions