SELECT tb_item.Item_id,tb_item.Item_code,tb_item.Item_detail,tb_item.Item_price,tb_item.Item_unit,tb_category.Cate_name,SUM(Redetail_amount) as qty,
(SELECT tb_item.Item_id,SUM(tb_receivedetail.Redetail_amount) FROM tb_receivedetail INNER JOIN tb_receive ON tb_receivedetail.Receive_id = tb_receive.Receive_id WHERE MONTH(Get_date) = '9' AND YEAR(Get_date) <= '2018') AS qty_2
FROM tb_receivedetail
INNER JOIN tb_receive ON tb_receivedetail.Receive_id = tb_receive.Receive_id
INNER JOIN tb_item ON tb_receivedetail.Item_id = tb_item.Item_id
INNER JOIN tb_category ON tb_item.Cate_id = tb_category.Cate_id
WHERE MONTH(Get_date) < '9' AND YEAR(Get_date) <= '2018'
GROUP BY Item_id
Field ที่ Select ใน Sub Query แบบนี้ น่าจะ Select ได้แค่ 1 Field นะครับ
Code (SQL)
(SELECT tb_item.Item_id,SUM(tb_receivedetail.Redetail_amount) FROM tb_receivedetail INNER JOIN tb_receive ON tb_receivedetail.Receive_id = tb_receive.Receive_id WHERE MONTH(Get_date) = '9' AND YEAR(Get_date) <= '2018') AS qty_2
ขอเพิ่มเติมนะครับ พอดีผมลืมใส่ Group by ใน sub select อีกที
Code (SQL)
SELECT tb_item.Item_id,tb_item.Item_code,tb_item.Item_detail,tb_item.Item_price,tb_item.Item_unit,tb_category.Cate_name,SUM(Redetail_amount) as qty,(SELECT SUM(Redetail_amount) FROM tb_receivedetail INNER JOIN tb_receive ON tb_receivedetail.Receive_id = tb_receive.Receive_id WHERE MONTH(Get_date) = '9' AND YEAR(Get_date) <= '2018' GROUP BY Item_id) AS qty_2
FROM tb_receivedetail
INNER JOIN tb_receive ON tb_receivedetail.Receive_id = tb_receive.Receive_id
INNER JOIN tb_item ON tb_receivedetail.Item_id = tb_item.Item_id
INNER JOIN tb_category ON tb_item.Cate_id = tb_category.Cate_id
WHERE MONTH(Get_date) < '9' AND YEAR(Get_date) <= '2018'
GROUP BY Item_id
SELECT tb.Item_id,tb.Item_code,tb.Item_detail,tb.Item_price,tb.Item_unit
, ct.Cate_name, SUM(rd.Redetail_amount) as qty, (
select sum(dt.redetail_amount) from tb_receivedetail dt, tb_receive cv
where dt.Receive_id = cv.Receive_id
and MONTH(cv.Get_date) < '9' AND YEAR(cv.Get_date) <= '2018'
and dt.item_id=tb.item_id
) as qty2
FROM tb_receivedetail rd, tb_receive rc, tb_item tb, tb_category ct
where rd.Receive_id = rc.Receive_id
and rd.Item_id = tb.Item_id
and tb.Cate_id = ct.Cate_id
and MONTH(rc.Get_date) < '9' AND YEAR(rc.Get_date) <= '2018'
GROUP BY tb.Item_id