how to get sum of each row and column
-
I used this code below to query and count the test result of OK,NG and RR.
select station_no,
count(case when TestResult='ok' then 'ok' end)as total_ok,
COUNT(case when TestResult='NG' then 'NG' end)as total_NG,
COUNT(case when TestResult='RR' then 'RR' end)as total_RR
from Computer_Part
where SQLDateTime >= '2018-07-02T08:00:00 AM'
and SQLDateTime <= '2018-07-03T07:59:00 AM'
group by Station_No
order by Station_No desc
here is the result of my query except the red color.
please help me modify my code to get the total sum column and the TOTAL below.
i got 150 by adding 136+0+14 etc.
i got 1140 by adding all total_ok column.etc
is that possible ?
thanks in advance.
-
This should mostly do what you want.
WITH Src
AS (SELECT station_no,
CASE WHEN TestResult = 'ok' THEN 1 END AS ok,
CASE WHEN TestResult = 'NG' THEN 1 END AS NG,
CASE WHEN TestResult = 'RR' THEN 1 END AS RR
FROM Computer_Part
WHERE SQLDateTime >= '2018-07-02T08:00:00 AM'
AND SQLDateTime <= '2018-07-03T07:59:00 AM'
)
SELECT
station_no,
COUNT(ok) AS TotalOK,
COUNT(NG) AS TotalOK,
COUNT(RR) AS TotalOK,
COUNT(ok) + COUNT(NG) + COUNT(RR) AS Total
FROM Src
GROUP BY ROLLUP (Station_No)
ORDER BY Station_No DESC;Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Sir thanks a lot !!
it works !!!! - BONITO - Monday, July 9, 2018 7:51 AM
Sir what about i want to add the Yield below .
yield = total_ok / total_sum
1140/1270=89.76%
thank you. -
That's probably easier to do in your app, wherever you display this data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Btw, I'd stronbly encourage you to use:
and SQLDateTime < '2018-07-03T08:00:00 AM'
rather than:
and SQLDateTime <= '2018-07-03T07:59:00 AM'SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply