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?