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?