Complex Select Query
-
There is a table name "ResultData" which following data : Rollno Result SchoolCode 1 First 1 2 First 1 3 Second 2 4 First 1 5 Fail 2 6 Third 2 7 Second 1 8 Fail 3 9 Fail 1 10 Second 2 I want the output like this : (School code wise count of Result) SchoolCode First Second Third Fail Total 1 3 1 0 1 5 2 0 2 1 1 4 3 0 0 0 1 1 Please write a single select query to get output. Thanks in advance Girish Kumar Sharma
-
There is a table name "ResultData" which following data : Rollno Result SchoolCode 1 First 1 2 First 1 3 Second 2 4 First 1 5 Fail 2 6 Third 2 7 Second 1 8 Fail 3 9 Fail 1 10 Second 2 I want the output like this : (School code wise count of Result) SchoolCode First Second Third Fail Total 1 3 1 0 1 5 2 0 2 1 1 4 3 0 0 0 1 1 Please write a single select query to get output. Thanks in advance Girish Kumar Sharma
I believe you want a "crosstab query". Apart from Microsoft products, I don't know if other vendors have it built-in. I don't believe it's part of the SQL standard... Here is what Microsoft Access help says about cross tab queries. " Syntax TRANSFORM aggfunction selectstatement PIVOT pivotfield [IN (value1[, value2[, ...]])] The TRANSFORM statement has these parts: Part Description aggfunction An SQL aggregate function that operates on the selected data. selectstatement A SELECT statement. pivotfield The field or expression you want to use to create column headings in the query's result set. value1, value2 Fixed values used to create column headings. "
-
I believe you want a "crosstab query". Apart from Microsoft products, I don't know if other vendors have it built-in. I don't believe it's part of the SQL standard... Here is what Microsoft Access help says about cross tab queries. " Syntax TRANSFORM aggfunction selectstatement PIVOT pivotfield [IN (value1[, value2[, ...]])] The TRANSFORM statement has these parts: Part Description aggfunction An SQL aggregate function that operates on the selected data. selectstatement A SELECT statement. pivotfield The field or expression you want to use to create column headings in the query's result set. value1, value2 Fixed values used to create column headings. "
-
Well, I thought it was included in SQL Server... Looks like didn't show-up until 2005. Here is one article that may help. http://builder.com.com/5100-6388_14-6143761.html[^] Once upon a time I wrote a lot of Perl code to dynamically do the same thing that the crosstab query does, but when I discovered crosstabs, I never went back. I don't know how to do it using CASE statements. sorry I can't help more. Maybe someone else can...
-
Well, I thought it was included in SQL Server... Looks like didn't show-up until 2005. Here is one article that may help. http://builder.com.com/5100-6388_14-6143761.html[^] Once upon a time I wrote a lot of Perl code to dynamically do the same thing that the crosstab query does, but when I discovered crosstabs, I never went back. I don't know how to do it using CASE statements. sorry I can't help more. Maybe someone else can...
You're correct, PIVOTs don't show up until 2005 in MSSQL
“Some have an idea that the reason we in this country discard things so readily is because we have so much. The facts are exactly opposite - the reason we have so much is simply because we discard things so readily. We replace the old in return for something that will serve us better.”--Alfred P. Sloan
-
Also check out http://www.oreillynet.com/pub/a/network/2004/12/17/crosstab.html?CMP=ILC-FV7511446129&ATT=2411[^] for a good explanation, and (possibly) a pre-written procedure you (might) be able to use.
Thanks to all of you and finally i used this query : SELECT SCHCODE,COUNT(CASE WHEN RESULT = 'First' THEN Result END) AS First,COUNT(CASE WHEN Result = 'Second' THEN Result END) AS Second,COUNT(CASE WHEN Result = 'Third' THEN Result END) AS Third,COUNT(CASE WHEN Result = 'Fail' THEN Result END) AS Fail,count(*) as Total FROM resultdata GROUP BY schcode
-
There is a table name "ResultData" which following data : Rollno Result SchoolCode 1 First 1 2 First 1 3 Second 2 4 First 1 5 Fail 2 6 Third 2 7 Second 1 8 Fail 3 9 Fail 1 10 Second 2 I want the output like this : (School code wise count of Result) SchoolCode First Second Third Fail Total 1 3 1 0 1 5 2 0 2 1 1 4 3 0 0 0 1 1 Please write a single select query to get output. Thanks in advance Girish Kumar Sharma
I'm not much of an SQL query expert, But I think that I have a solution
SELECT SchoolCode, COUNT(First) As TotalFirst, COUNT(Second) As TotalSecond, COUNT(Third) As TotalThird, COUNT(Fail) As TotalFail, (COUNT(First) + COUNT(Second) + COUNT(Third) +COUNT(Fail)) As TotalTotal FROM ResultData GROUP BY (SchoolCode) I havent tested it.Post a reply to let every body know if it works.