sql select issue (mysql)

Question

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?

Just join all 3 tables, add a WHERE clause for the date, and use SUM() and GROUP BY item.id. — Barmar
– Barmar, Commented Dec 16, 2016 at 21:31
@Barmar this worked for me. THANKS A LOT! The final query was something link this 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; — TroniPM
– TroniPM, Commented Dec 17, 2016 at 0:04
@PMateus Post it as an answer if it's different from the other answers that were already posted. Otherwise, accept one of the answers. — Barmar
– Barmar, Commented Dec 17, 2016 at 0:15

Ahmad.Tr · Accepted Answer · 2016-12-16 21:31:38Z

1

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

answered Dec 16, 2016 at 21:31

Ahmad.Tr

7567 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jophab · Accepted Answer · 2016-12-17 02:14:39Z

1

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.

answered Dec 17, 2016 at 2:14

jophab

5,54714 gold badges45 silver badges61 bronze badges

Comments

score 1 · Accepted Answer · 2016-12-17 11:22:58Z

1

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;

edited Dec 17, 2016 at 11:22

answered Dec 16, 2016 at 21:51

user6935989

Comments

TroniPM · Accepted Answer · 2016-12-17 18:08:56Z

1

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;

answered Dec 17, 2016 at 18:08

TroniPM

3293 silver badges13 bronze badges

Collectives™ on Stack Overflow

sql select issue (mysql)

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related