SELECT tblProductType.pdtID, tblProductType.Barcode, tblProductType.ProductType_1, (SELECT MONTH( tblProductReceive.[_Date]) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID) AS m ,
(SELECT sum( tblProductReceive.Count) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID ) AS sum
FROM tblProductType;
คือผมจะแยก รายการ แต่ละรายการตามเดือน ครับ
Tag : .NET, C#, VS 2012 (.NET 4.x), VS 2013 (.NET 4.x)
select p.pdtID,p.Barcode,p.ProductType_1,
month(t.[_Date]) as _Month,
sum(t.[Count]) as _Sum
from tblProductType p left join
tblProductReceive t on p.pdtID=t.pdtID
group by p.pdtID,p.Barcode,p.ProductType_1,month(t.[_Date])
SELECT tblProductType.pdtID, tblProductType.Barcode, tblProductType.ProductType_1, (iif(isnull( (SELECT sum( tblProductReceive.Count) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID )),0, (SELECT sum( tblProductReceive.Count) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID )) ) AS sumR, (iif(isnull( (SELECT sum( tblProductReceive.SumPrice) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID )),0, (SELECT sum( tblProductReceive.SumPrice) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID )) ) AS sumRP, (iif(isnull((SELECT sum( tblProductSale.Count) from tblProductSale where tblProductSale.pdtID = tblProductType.pdtID )),0,(SELECT sum( tblProductSale.Count) from tblProductSale where tblProductSale.pdtID = tblProductType.pdtID ))) AS sumS, (iif(isnull((SELECT sum( tblProductSale.SumPrice) from tblProductSale where tblProductSale.pdtID = tblProductType.pdtID )),0,(SELECT sum( tblProductSale.SumPrice) from tblProductSale where tblProductSale.pdtID = tblProductType.pdtID ))) AS sumSP, (sumR-sumS) AS sumC
FROM tblProductType;
ผมก็เลย ทำเป็นแบบ sum แต่ละรายการ ในแต่ละเดือน ครับ
ถ้า เรา sum แต่ละรายการ
Code (SQL)
SELECT tblProductType.pdtID, tblProductType.Barcode, tblProductType.ProductType_1, (iif(isnull( (SELECT sum( tblProductReceive.Count) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID )),0, (SELECT sum( tblProductReceive.Count) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID )) ) AS sumR
FROM tblProductType;
จะได้แบบนี้ครับ
คราวนี้พอเราจะเอาแต่ละเดือนก็ต้องเพิ่ม
Code (SQL)
(SELECT MONTH( tblProductReceive.[_Date]) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID) AS m
น่าจะ Error จากตรงนี้แหละครับ
แต่ถ้า เอาแค่ Code (SQL)
SELECT MONTH( tblProductReceive.[_Date]) from tblProductReceive
แบบนี้ได้ครับ
แต่ถ้า Code (SQL)
SELECT tblProductType.pdtID, tblProductType.Barcode, tblProductType.ProductType_1, (SELECT MONTH( tblProductReceive.[_Date]) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID) AS m ,
(SELECT sum( tblProductReceive.Count) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID ) AS sum
FROM tblProductType;
SELECT t.pdtID, t.Barcode, t.ProductType_1, d.[_Date] AS m, sum(d.[Count]) AS s
FROM tblProductReceive AS d
left join tblProductType As tb1 on t.pdtID = d.pdtID
order by YEAR(d.[_Date]), MONTH(d.[_Date])
SELECT tblProductType.pdtID, tblProductType.Barcode, tblProductType.ProductType_1, (SELECT MONTH( tblProductReceive.[_Date]) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID) AS m ,
(SELECT sum( tblProductReceive.Count) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID ) AS sum
FROM tblProductType;
ผมคิดว่ามาจากตรง AS sum ครับ
งั้นลองแก้เป็น AS sumXXXX ดูครับ
SELECT tblProductType.pdtID, tblProductType.Barcode, tblProductType.ProductType_1, (SELECT MONTH( tblProductReceive.[_Date]) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID) AS m ,
(SELECT sum( tblProductReceive.Count) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID ) AS sum
FROM tblProductType;
SELECT tblProductType.pdtID, tblProductType.Barcode, tblProductType.ProductType_1, (SELECT MONTH( tblProductReceive.[_Date]) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID) AS m ,
(SELECT sum( tblProductReceive.Count) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID ) AS sumRRR
FROM tblProductType;
SELECT tblProductType.pdtID, tblProductType.Barcode, tblProductType.ProductType_1,
(SELECT sum( tblProductReceive.Count) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID ) AS sumRRR
FROM tblProductType;
ใช้ SUM แต่จะเอา COlumn อื่นมาแสดงผลด้วย ไม่ได้อยู่แล้วครับ วิธีแก้ปัญหามีอยู่ 2 ทาง
1. group by ทุก column ยกเว้น column ที่ SUM
2. ใช้ sub query แบบที่คุณทำนั่นแหละครับ
select p.pdtID,p.Barcode,p.ProductType_1,
month(t.[_Date]) as _Month,year(t.[_Date]) as _Year,
sum(t.[Count]) as _SumRRR
from tblProductType p left join
tblProductReceive t on p.pdtID=t.pdtID
-- Access เข้าใช้ยังไง ใส่ # หัวท้ายใช่ไหมครับ
where t.[_Date] between #2017-01-01# and #2017-12-31#
group by p.pdtID,p.Barcode,p.ProductType_1,month(t.[_Date])
select p.pdtID,p.Barcode,p.ProductType_1,
month(t.[_Date]) as _Month,
sum(t.[Count]) as _SumRRR
from tblProductType p left join
tblProductReceive t on p.pdtID=t.pdtID
where t.[_Date] between #2017-01-01# and #2017-12-31#
group by p.pdtID,p.Barcode,p.ProductType_1,month(t.[_Date]);
SELECT t.pdtID, t.Barcode, t.ProductType_1,d.m,d.s
From (select pdtID,d.[_Date] AS m, sum([Count) as s
from tblProductReceive group by pdtID,[_Date]) AS d
left join tblProductType As t on t.pdtID = d.pdtID
Load balance : Server 03
