Post reply

how to get sum of each row and column

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

  • 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

  • July 9, 2018 at 7:51 am

    #1996784

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

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

  • 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

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply