SQL Select puzzle to count failures and totals for each item


Suppose I have a table of 2 columns. One is the name of an item, the other a value indicating Failure or Success.


 


Can I create a single SQL statement to get a result with 3 columns: Name, # of Total occurrences, # of Failed occurrences ?


 


 


Sample data:


 


  NAME       FAILED


  n001            


  n001            


  n001       Y    


  n002            


  n002       Y     


  n002            


  n003       Y    


  n003            


  n003       Y    


  n004            


  n004       Y    


  n004            


  n005       Y    


  n005            


  n005       Y    


  n006            


  n006       Y    


  n006            


  n007       Y    


  n007            


  n007       Y    


  n008            


  n008       Y    


  n008            


  n009       Y    


  n009            


  n009       Y    


  n010            


  n010       Y    


  n010             


  n011       Y    


  n011            


  n011       Y    


 


 


 


The code below creates the sample data and attempts to get the results with a single SQL statement.


 


 


CREATE CURSOR MyData (name c(10),failed c(1))


INDEX ON name TAG name


SET ENGINEBEHAVIOR 90


FOR j = 1 TO 3


          FOR i = 1 TO 11


                   INSERT INTO MyData (name,failed) VALUES (“n”+PADL(i,3,”0″),IIF(MOD(RECNO(),2)=0,”Y”,” “))


          ENDFOR


ENDFOR


LOCATE


BROWSE LAST NOWAIT


 


SELECT name,count(*) failed, 0 success from MyData GROUP BY name WHERE failed=”Y”  UNION ;


          SELECT name, 0 failed, count(*)  success from MyData GROUP BY name WHERE failed=” “  ;


          INTO CURSOR result 


BROWSE LAST NOWAIT


 


 


The result is almost what I want, but it has 2 entries for each name: one with 0 as number of Failures, and the # of successes, and vice versa:


 


 


  NAME           FAILED    SUCCESS


  n001                0          2


  n001                1          0


  n002                0          2


  n002                1          0


  n003                0          1


  n003                2          0


  n004                0          2


  n004                1          0


  n005                0          1


  n005                2          0


  n006                0          2


  n006                1          0


  n007                0          1


  n007                2          0


  n008                0          2


  n008                1          0


  n009                0          1


  n009                2          0


  n010                0          2


  n010                1          0


  n011                0          1


  n011                2          0


 


 


The desired result will have the 2 records per row combined with the FAILED and SUCCESS columns summed


 


Anyone?

Comments (19)

  1. Lyle says:

    SELECT name,

    SUM(CASE WHEN failed = ‘Y’ THEN 1 ELSE 0 END) AS failed,

    SUM(CASE WHEN failed = ‘Y’ THEN 0 ELSE 1 END) AS succeeded,

    COUNT(*) AS total

    FROM MyData

    GROUP BY name

  2. leeb says:

    SELECT [name], SUM([failed]) AS [failed], SUM([success]) AS [success]

    FROM (

    SELECT [name],

    CASE WHEN [failed] = ‘Y’ THEN 1 ELSE 0 END AS [failed],

    CASE WHEN [failed] <> ‘Y’ THEN 1 ELSE 0 END AS [success]

    FROM [MyData]

    ) AS [r]

    GROUP BY [name]

    ORDER BY [name]

  3. Prince Devasitham says:

    Hi Calvin

    you can use the following query if you are using SQL Server 2005.

    here the puzzle is the table and it has column id and status as columns.

    select p1.id [Id],count(*)[Total Count],ca.success [Total Success]

    from puzzle as p1

    cross apply (select count(*) success from puzzle p2 where  p1.id = p2.id and status is not null) as ca

    group by id,ca.success

    order by id

    This is my first reply to any msdn blog, i feel happy to help to out.

    Thanks for the oppurtunity

    prince.devasitham@gmail.com

    Prince Devasitham.

  4. leeb says:

    Didn’t see total requirement…

    SELECT [name], SUM([failed]) AS [failed], SUM([success]) AS [success],

    SUM([failed] + [success]) AS [total]

    FROM (

    SELECT [name],

    CASE WHEN [failed] = ‘Y’ THEN 1 ELSE 0 END AS [failed],

    CASE WHEN [failed] <> ‘Y’ THEN 1 ELSE 0 END AS [success]

    FROM [MyData]

    ) AS [r]

    GROUP BY [name]

    ORDER BY [name]

  5. Stuart Dunkeld says:

    Hi Calvin

    select name,

    count(*) as total,

    sum(iif(empty(failed), 1, 0)) as failed,

    sum(iif(failed = "Y", 1, 0)) as success

    from mydata

    group by name

  6. bill drew says:

    SELECT name,SUM(IIF(FAILED ="Y",1,0)) failed,SUM(IIF(FAILED =" ",1,0)) success FROM  mydata GROUP BY name INTO CURSOR result  

  7. Michael Staroselsky says:

    SELECT tt.name, tt.tot, NVL(f.fail,0)as failed

    FROM (select name, COUNT(*) as tot FROM MyData   GROUP BY 1 ) as tt

    LEFT JOIN (select name , COUNT(*) as fail  FROM  MyData   WHERE  failed=’Y’ GROUP BY 1) as f  ON  tt.name==f.name

  8. Calvin_Hsia says:

    Wow: so many great suggestions. Thanks everybody!

    Here’s what it looks like so far, and it works great!

    The code is in the middle of a TextMerge, getting data from SQL Server and outputting HTML

    <<IIF(""=cFilter,"","Filter = ‘"+cFilter+"’")>>

    cFilterExpr=IIF(""=cFilter,"","Where ATC(‘"+cFilter+"’,SuiteName)>0")

    IF ""=cSuiteName && Main page: no specified suite

    IF SQLEXEC(hConn,"Select SuiteName,failed,duration from trun where runtime >= CAST(?cDate as datetime)","SuiteNames") > 0

    SELECT ;

    ‘<a href=trun.asp?mode=’+SuiteName+’>’+SuiteName+’ </a>’  as SuiteName, ;

    COUNT(*) as Total, ;

    SUM(IIF(failed="Y",1,0)) as Failed, ;

    AVG(duration) as AvgDurationSecs  &cFilterExpr;

    GROUP BY SuiteName ;

    ORDER BY SuiteName ;

    FROM SuiteNames INTO CURSOR result

    CursorToHTML(‘TRUN Suites since ‘ + cDate+ ‘: Total # = ‘+TRANSFORM(_tally),"")

    SELECT SUM(total),SUM(failed) FROM result INTO CURSOR summary

    CursorToHTML(‘Summary’,"")

  9. Tod McKenna says:

    My first attempt was almost exactly like Bill Drew’s. But not to be left out, I thought I’d try again using a different approach (warning: this is pretty ugly!):

    SELECT md1.name, md1.success, md2.failed ,;  

       md1.success + md2.failed as summed;

     FROM (SELECT name, COUNT(*) as success

           FROM mydata mda

           WHERE EMPTY(failed);

           GROUP BY name) md1 ;

     JOIN (SELECT name, COUNT(*) as failed;

           FROM mydata mda ;

           WHERE failed = "Y" ;

           GROUP BY name) md2 ;

       ON md1.name = md2.name ;

    INTO CURSOR result

  10. Tod McKenna says:

    Of course, now I see that this is almost the same thing that Michael Staroselsky came up with. And his takes into account the case where nothing failed! *sigh*

    This revision satisfies conditions where nothing failed, everything failed, or some combo. Bill Drew’s simpler solution does so as well:

    SELECT md1.name,;

       md1.summed-NVL(md2.failed,0) as success,;

       NVL(md2.failed,0) as failed , md1.summed;

     FROM ;

         (SELECT name, COUNT(*) as summed ;

          FROM mydata mda GROUP BY name) md1 ;

     LEFT JOIN ;

         (SELECT name, COUNT(*) as failed ;

          FROM mydata mda WHERE failed = "Y" ;

          GROUP BY name) md2 ;

       ON md1.name = md2.name ;

    INTO CURSOR result

  11. Hank Fay says:

    This works in VFP9; should work in SQL.

    SELECT a.name,  ;

    (SELECT COUNT(*) as npassed FROM mydata b WHERE b.name = a.name AND b.failed # "Y"), ;

    (SELECT COUNT(*) as nfailed FROM mydata c WHERE c.name = a.name AND c.failed = "Y") ;

    FROM mydata a GROUP BY name

  12. Paul says:

    SELECT name, COUNT(*), SUM(IIF(failed, 1, 0)) FROM mydata GROUP BY name

  13. Paul says:

    Or, in the desired format:

    SELECT name, SUM(IIF(failed, 0, 1)) AS success, SUM(IIF(failed, 1, 0)) AS failed FROM mydata GROUP BY name

  14. Suppose I have a table of test results with at least 4 columns: ID (unique integer) SuiteName (name of

  15. Suppose I have a table of test results with at least 4 columns: ID (unique integer) SuiteName (name of

  16. Fabio Lunardon says:

    Another

    * THIS REPORT A ERROR, BUT IT IS AN WRONG ERROR because NULL is ignored for every datatype

    SELECT name;

    ,count(ICASE(failed="Y",.T.)) failed;

    ,count(ICASE(failed=" ",.T.)) success ;

    from MyData GROUP BY name;

    INTO CURSOR result  

    * infact mixed datatypes works !

    SELECT name;

    ,count(IIF(failed="Y",.T.,CAST(NULL AS I))) failed;

    ,count(IIF(failed=" ",.T.,CAST(NULL AS I))) success ;

    from MyData GROUP BY name;

    INTO CURSOR result

  17. matar_en says:

    <a href= http://index5.000-tn.com >ohio high school girls softball tournament results</a> <a href= http://index1.000-tn.com >blonde nites dress</a> <a href= http://index2.000-tn.com >revolutionary map of massechussetts</a> <a href= http://index4.000-tn.com >massachusetts motorcycle blessings</a> <a href= http://index3.000-tn.com >massachusetts automart</a>

  18. sasha says:

    <a href= http://index3.7askabout.com >precor 9.25i</a> <a href= http://index1.7askabout.com >i am the warrior</a> <a href= http://index2.7askabout.com >atiradion</a> <a href= http://index4.7askabout.com >euston manifesto</a> <a href= http://index5.7askabout.com >police officer nicole maile west palm beach</a>

  19. sasha says:

    <a href= http://index3.7askabout.com >precor 9.25i</a> <a href= http://index1.7askabout.com >i am the warrior</a> <a href= http://index2.7askabout.com >atiradion</a> <a href= http://index4.7askabout.com >euston manifesto</a> <a href= http://index5.7askabout.com >police officer nicole maile west palm beach</a>