how to get sum of each row and column

• BONITO

  SSC-Addicted

  Points: 425

  More actions

  July 9, 2018 at 7:02 am

  #414335

  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.

  

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 9, 2018 at 7:30 am

  #1996780

  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 recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• BONITO

  SSC-Addicted

  Points: 425

  More actions

  July 9, 2018 at 7:51 am

  #1996784

  Sir thanks a lot !! 
  it works !!!!

• BONITO

  SSC-Addicted

  Points: 425

  More actions

  July 9, 2018 at 7:57 am

  #1996786

  BONITO - Monday, July 9, 2018 7:51 AM

  Sir thanks a lot !! 
  it works !!!!

  Sir what about i want to add the Yield below . 
  yield = total_ok / total_sum 
  1140/1270=89.76% 
  thank you.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 9, 2018 at 7:59 am

  #1996787

  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 recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• ScottPletcher

  SSC Guru

  Points: 101098

  More actions

  July 9, 2018 at 10:24 am

  #1996820

  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".