Selling mysql products
I would like to ask a question here ... let me explain it ... here I have 3 tables, 1.transaction_table 2.transaction_detail 3.item
No, my problem here is that I want to know the sales of this month (2013-01) for an example, here's my table structure ...
1 Transaction table
--------------------------
idTransaction | date | idUser | idCustomer | total | NOSC
--------------------------------------------------------------------
1 | 2013-01-01 | 3 | 4 | 500000 | 1CO
2 | 2013-01-01 | 3 | 5 | 450000 | 2CO
3 | 2013-01-01 | 3 | 6 | 250000 | 3CO
2 transaction_detail
-----------------------------------------------------------------
idTransaction | idItem | qty | price
----------------------------------------
1 | 1 | 2 | 250000
2 | 2 | 1 | 250000
2 | 3 | 1 | 200000
3 | 1 | 1 | 250000
3 Position table
idItem | Name Item
---------------------------------------
1 | glass
2 | Medicine A
3 | Medicine B
4 Client
idCustomer | Name Customer
--------------------------------------
4 | Abuh
5 | Abeh
6 | ABooh
So, based on this table, I would like to get data like this ....
Name Item | Nosc | Customer | Sold Quantity | @ Price | Total
---------------------------------------------
Glass | 1CO | Abuh |2 | 250000 | 500000
Glass | 3CO | ABooh |1 | 250000 | 250000
Medicine A| 2CO | Abeh |1 | 250000 | 250000
Medicine B| 2CO | Abeh |1 | 200000 | 200000
Can anyone help me?
+3
source to share
2 answers
SELECT b.`name Item`,
a.qty `Sold Quantity`,
a.price `@ price`,
(a.qty * a.price) Total
FROM transaction_detail a
INNER JOIN Item b
ON a.idItem = b.idItem
The above query is based on the entries and results provided in the example. Follow-up question (s): is it likely that a particular Item may be sold at different prices? If so, how do you calculate it?
UPDATE 1
SELECT b.`name Item`,
SUM(a.qty) `Sold Quantity`,
a.price `@ price`,
(SUM(a.qty) * a.price) Total,
c.Date
FROM transaction_detail a
INNER JOIN Item b
ON a.idItem = b.idItem
INNER JOIN `transaction` c
ON a.idtransaction = c.idTransaction
GROUP BY b.idItem, b.`name Item`, a.price, c.Date
UPDATE 2
SELECT b.`name Item`,
SUM(a.qty) `Sold Quantity`,
a.price `@ price`,
(SUM(a.qty) * a.price) Total,
d.`Name Customer`
FROM transaction_detail a
INNER JOIN Item b
ON a.idItem = b.idItem
INNER JOIN `transaction` c
ON a.idtransaction = c.idTransaction
INNER JOIN Customer d
ON d.idCustomer = c.idCustomer
GROUP BY b.idItem, b.`name Item`, a.price,
MONTH(c.Date), YEAR(c.Date), d.`Name Customer`
+4
source to share
select it.item_name,td.qty,td.price,t.total
from transaction as t,transaction_detail as td,item_table as it
where t.idTransaction=td.idTransaction
and td.idItem=it.idItem
and t.date between 'YEAR(CURDATE())."-".MONTH(CURDATE())."-"."01"'
and 'YEAR(CURDATE())."-".MONTH(CURDATE())."-"."31"'
+1
source to share