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
Anonymous
May 24, 2007
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 nameAnonymous
May 24, 2007
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]Anonymous
May 24, 2007
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.Anonymous
May 24, 2007
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]Anonymous
May 24, 2007
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 nameAnonymous
May 25, 2007
SELECT name,SUM(IIF(FAILED ="Y",1,0)) failed,SUM(IIF(FAILED =" ",1,0)) success FROM mydata GROUP BY name INTO CURSOR resultAnonymous
May 25, 2007
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.nameAnonymous
May 25, 2007
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',"")Anonymous
May 29, 2007
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 resultAnonymous
May 29, 2007
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 resultAnonymous
May 31, 2007
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 nameAnonymous
June 10, 2007
SELECT name, COUNT(*), SUM(IIF(failed, 1, 0)) FROM mydata GROUP BY nameAnonymous
June 10, 2007
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 nameAnonymous
July 16, 2007
Suppose I have a table of test results with at least 4 columns: ID (unique integer) SuiteName (name ofAnonymous
July 16, 2007
Suppose I have a table of test results with at least 4 columns: ID (unique integer) SuiteName (name ofAnonymous
October 11, 2007
AnotherTHIS 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
Anonymous
July 01, 2008
<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>Anonymous
July 15, 2008
<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>Anonymous
July 15, 2008
<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>