ผมต้องการเขียนCode SQL COUNT แบบหลายเงื่อนไข แต่พอเขียนได้ แต่เจ้า GROUP BY ดันไม่ยอมแบ่งกลุ่มให้ ใครรู้แนะนำทีครับ
ข้างล่างคือ Code ที่ผมคิดได้ตอนนี้ครับ
Code (SQL)
SELECT
(SELECT COUNT(projectID) From project WHERE moneyYearSupport = @moneyYearSupport AND projectState = 1)AS CountProject1 ,
(SELECT COUNT(projectID) From project WHERE moneyYearSupport = @moneyYearSupport AND projectState = 2)AS CountProject2 ,
(SELECT COUNT(projectID) From project WHERE moneyYearSupport = @moneyYearSupport AND projectState = 3)AS CountProject3 ,
(SELECT SUM(moneyBath) From project WHERE moneyYearSupport = @moneyYearSupport AND projectState = 1) AS SummoneyBath1 , strategyID
FROM project
GROUP BY strategyID
Tag : Ms SQL Server 2005, Ms SQL Server 2008, Ms SQL Server 2012
SELECT strategyID,
COUNT(P.projectID) AS CountProjecttotal,
COUNT(case when projectState = 1 then 1 else null end) as CountProject1,
COUNT(case when projectState = 2 then 1 else null end) as CountProject2,
COUNT(case when projectState = 3 then 1 else null end) as CountProject3,
SUM(P.moneyBath ) AS SummoneyBathtotal,
SUM(case when projectState = 1 then 1 else null end) as SummoneyBath1,
SUM(T.disbursed) AS Sumdisbursed
FROM project AS P
LEFT OUTER JOIN project_trace AS T ON (P.projectID = T.projectID)
WHERE moneyYearSupport = @moneyYearSupport
GROUP BY strategyID
Date :
2014-03-08 14:50:46
By :
ต้น
No. 2
Guest
ได้แล้ว เหอๆ Code (SQL)
SELECT strategyID,
COUNT(P.projectID) AS CountProjecttotal,
COUNT(case when projectState = 1 then P.projectID else null end) as CountProject1,
COUNT(case when projectState = 2 then P.projectID else null end) as CountProject2,
COUNT(case when projectState = 3 then P.projectID else null end) as CountProject3,
SUM(moneyBath ) AS SummoneyBathtotal,
SUM(case when projectState = 1 then moneyBath else null end) as SummoneyBath1,
SUM(T.disbursed) AS Sumdisbursed
FROM project AS P
LEFT OUTER JOIN project_trace AS T ON (P.projectID = T.projectID)
WHERE moneyYearSupport = @moneyYearSupport
GROUP BY strategyID
Load balance : Server 05
