query help

Question

query help

yogi123

SSCrazy

Points: 2932
More actions
August 7, 2012 at 9:10 pm

#264791

hello friends
I need one request,
here is DLL
create table #temp (Student_ID char(9),
Sex char(1),
RaceCode varchar(5),
Lunch int,
Student_ESL int,
GIEP int,
IEP int,
SchoolCode int)
Insert into #temp values ('004242726','F','Black',1,0,0,0,204)
Insert into #temp values ('004242734','F','Black',0,1,0,0,356)
Insert into #temp values ('004242777','M','White',1,0,0,0,141)
Insert into #temp values ('004242793','F','Black',1,0,0,1,164)
Insert into #temp values ('004242831','M','White',0,0,0,1,141)
Insert into #temp values ('004242866','M','White',1,0,0,0,204)
Insert into #temp values ('004242882','M','White',0,1,0,0,141)
Insert into #temp values ('004242890','F','Black',0,0,0,0,204)
Insert into #temp values ('004242971','F','Black',0,0,0,1,164)
Insert into #temp values ('004243129','F','Black',0,0,0,1,204)
Insert into #temp values ('004243137','M','Black',0,0,0,0,164)
Insert into #temp values ('004243188','M','Black',1,0,0,0,204)
Insert into #temp values ('004243196','F','Other',1,1,0,0,204)
Insert into #temp values ('004243285','M','Other',0,0,0,1,204)
Insert into #temp values ('004243293','F','Other',0,0,0,1,164)
Insert into #temp values ('004243323','M','Other',0,0,0,1,204)
Insert into #temp values ('004243447','F','Black',1,1,0,0,204)
Insert into #temp values ('004243455','M','Black',0,0,0,0,164)
so the desired output should be
if we filter based on schoolcode for example 204 then output should be
BLACK WHITE OTHER
MALE 1 12
FEMALE 4 01
IEP 1 02
GIEP 0 00
LUNCH 3 11
ESL 1 01
so i need to count all my column data like sex,IEP,GIEP,LUNCH,ESL based on race code.
for IEP,GIEP,LUNCH,ESL, when the value = 1 then we need to count else not count.
I am thinking to do with pivoting and do union but if is there any easy way to solve this please let me know.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

MyDoggieJessie SSC-Forever Points: 44484 More actions · Answer 1

Not certain why you'd want to do this type of thing in SQL instead of Reporting Services or Excel (but I'm sure there's reasons ;-)) - here's a great article that will point you in the proper direction

http://www.sqlservercentral.com/articles/Stairway+Series/87629/

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

subbu1 Hall of Fame Points: 3281 More actions · Answer 2

pls try below code

select case when Sex='M' THEN 'MALE' WHEN Sex='F' THEN 'FEMALE' END COLUMN_H,COUNT(CASE WHEN RaceCode='Black' THEN Sex END) AS Black,

COUNT(CASE WHEN RaceCode='White' THEN Sex END) AS White,

COUNT(CASE WHEN RaceCode='Other' THEN Sex END) AS Other from #temp

where SchoolCode='204'

GROUP BY (case when Sex='M' THEN 'MALE' WHEN Sex='F' THEN 'FEMALE' END)

UNION

select emp,Black,White,Other from

(SELECT RaceCode,emp,case when ord=1 then ord end ord FROM

(SELECT RaceCode,Student_ESL as ESL,GIEP,IEP,Lunch FROM #temp where SchoolCode='204')K

UNPIVOT

(ord FOR emp IN (ESL,GIEP,IEP,Lunch)

) AS pvt)k

pivot

(count(ord) FOR RaceCode IN (Black,White,Other))pvt

Dwain Camps SSC Guru Points: 86908 More actions · Answer 3

You can try something like this, although I don't get the same counts as you do:

;WITH Unpivoted AS (

SELECT RaceCode, Code, Value

FROM #Temp

CROSS APPLY (

VALUES ('LUNCH', Lunch), ('ESL', Student_ESL), ('GIEP', GIEP), ('IEP', IEP)

) a (Code, Value)

)

SELECT Category=CASE Sex WHEN 'M' THEN 'MALE' ELSE 'FEMALE' END)

,BLACK=COUNT(CASE WHEN RaceCode = 'Black' THEN 1 END)

,WHITE=COUNT(CASE WHEN RaceCode = 'White' THEN 1 END)

,OTHER=COUNT(CASE WHEN RaceCode = 'Other' THEN 1 END)

FROM #Temp

GROUP BY Sex

UNION ALL

SELECT Code

,BLACK=SUM(CASE WHEN RaceCode = 'Black' THEN Value ELSE 0 END)

,WHITE=SUM(CASE WHEN RaceCode = 'White' THEN Value ELSE 0 END)

,OTHER=SUM(CASE WHEN RaceCode = 'Other' THEN Value ELSE 0 END)

FROM Unpivoted

GROUP BY Code

I count 9 males and 9 females in your data and my query above returns this.

Note that I've used the "Other UNPIVOT" because it is usually faster: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

MyDoggieJessie (8/7/2012)

Not certain why you'd want to do this type of thing in SQL instead of Reporting Services or Excel (but I'm sure there's reasons ;-)) - here's a great article that will point you in the proper direction

http://www.sqlservercentral.com/articles/Stairway+Series/87629/%5B/quote%5D

Let's make this easier for others, no cut and paste required:

http://www.sqlservercentral.com/articles/Stairway+Series/87629/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

dwain.c (8/8/2012)
You can try something like this, although I don't get the same counts as you do:
;WITH Unpivoted AS (
SELECT RaceCode, Code, Value
FROM #Temp
CROSS APPLY (
VALUES ('LUNCH', Lunch), ('ESL', Student_ESL), ('GIEP', GIEP), ('IEP', IEP)
) a (Code, Value)
)
SELECT Category=CASE Sex WHEN 'M' THEN 'MALE' ELSE 'FEMALE' END)
,BLACK=COUNT(CASE WHEN RaceCode = 'Black' THEN 1 END)
,WHITE=COUNT(CASE WHEN RaceCode = 'White' THEN 1 END)
,OTHER=COUNT(CASE WHEN RaceCode = 'Other' THEN 1 END)
FROM #Temp
GROUP BY Sex
UNION ALL
SELECT Code
,BLACK=SUM(CASE WHEN RaceCode = 'Black' THEN Value ELSE 0 END)
,WHITE=SUM(CASE WHEN RaceCode = 'White' THEN Value ELSE 0 END)
,OTHER=SUM(CASE WHEN RaceCode = 'Other' THEN Value ELSE 0 END)
FROM Unpivoted
GROUP BY Code
I count 9 males and 9 females in your data and my query above returns this.
Note that I've used the "Other UNPIVOT" because it is usually faster: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

Because the counts shown by the OP were for a specific school code, not all the records.

Dwain Camps SSC Guru Points: 86908 More actions · Answer 6

Thanks Lynn! Apparently if I could read, I would've come up with this version instead:

;WITH Unpivoted AS (

SELECT SchoolCode, RaceCode, Code, Value

FROM #Temp

CROSS APPLY (

VALUES ('LUNCH', Lunch), ('ESL', Student_ESL), ('GIEP', GIEP), ('IEP', IEP)

) a (Code, Value)

)

SELECT Sex=CASE Sex WHEN 'M' THEN 'MALE' ELSE 'FEMALE' END

,BLACK=COUNT(CASE WHEN RaceCode = 'Black' THEN 1 END)

,WHITE=COUNT(CASE WHEN RaceCode = 'White' THEN 1 END)

,OTHER=COUNT(CASE WHEN RaceCode = 'Other' THEN 1 END)

FROM #Temp

WHERE SchoolCode = 204

GROUP BY Sex

UNION ALL

SELECT Code

,BLACK=SUM(CASE WHEN RaceCode = 'Black' THEN Value ELSE 0 END)

,WHITE=SUM(CASE WHEN RaceCode = 'White' THEN Value ELSE 0 END)

,OTHER=SUM(CASE WHEN RaceCode = 'Other' THEN Value ELSE 0 END)

FROM Unpivoted

WHERE SchoolCode = 204

GROUP BY Code

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Cadavre SSC-Forever Points: 41690 More actions · Answer 7

dwain.c (8/8/2012)
Thanks Lynn! Apparently if I could read, I would've come up with this version instead:
;WITH Unpivoted AS (
SELECT SchoolCode, RaceCode, Code, Value
FROM #Temp
CROSS APPLY (
VALUES ('LUNCH', Lunch), ('ESL', Student_ESL), ('GIEP', GIEP), ('IEP', IEP)
) a (Code, Value)
)
SELECT Sex=CASE Sex WHEN 'M' THEN 'MALE' ELSE 'FEMALE' END
,BLACK=COUNT(CASE WHEN RaceCode = 'Black' THEN 1 END)
,WHITE=COUNT(CASE WHEN RaceCode = 'White' THEN 1 END)
,OTHER=COUNT(CASE WHEN RaceCode = 'Other' THEN 1 END)
FROM #Temp
WHERE SchoolCode = 204
GROUP BY Sex
UNION ALL
SELECT Code
,BLACK=SUM(CASE WHEN RaceCode = 'Black' THEN Value ELSE 0 END)
,WHITE=SUM(CASE WHEN RaceCode = 'White' THEN Value ELSE 0 END)
,OTHER=SUM(CASE WHEN RaceCode = 'Other' THEN Value ELSE 0 END)
FROM Unpivoted
WHERE SchoolCode = 204
GROUP BY Code

Do you not like GROUP BY ROLLUP ?

e.g.

SELECT Code, BLACK, WHITE, OTHER

FROM (SELECT Code,

BLACK=SUM(CASE WHEN RaceCode = 'Black' AND Value = 1 THEN 1 ELSE 0 END),

WHITE=SUM(CASE WHEN RaceCode = 'White' AND Value = 1 THEN 1 ELSE 0 END),

OTHER=SUM(CASE WHEN RaceCode = 'Other' AND Value = 1 THEN 1 ELSE 0 END),

MAX(Pos) AS Pos

FROM (SELECT SchoolCode, RaceCode, Code, Value, Pos

FROM #temp

CROSS APPLY (VALUES ('LUNCH', Lunch, 5), ('ESL', Student_ESL, 6), ('GIEP', GIEP, 4), ('IEP', IEP, 3),

('Male', CASE WHEN Sex = 'M' THEN 1 ELSE 0 END, 1),

('Female', CASE WHEN Sex = 'F'THEN 1 ELSE 0 END, 2)

) a(Code, Value, Pos)) a

WHERE SchoolCode = 204

GROUP BY ROLLUP (Code)

) a

WHERE Code IS NOT NULL

ORDER BY Pos;

Results in: -

Code BLACK WHITE OTHER

------ ----------- ----------- -----------

Male 1 1 2

Female 4 0 1

IEP 1 0 2

GIEP 0 0 0

LUNCH 3 1 1

ESL 1 0 1

I've only added the ORDER BY to replicate the ordering that the OP posted.

I'll set up a big performance test in a little while to test the difference, but my gut feeling is that the GROUP BY ROLLUP will be faster.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Cadavre SSC-Forever Points: 41690 More actions · Answer 8

Performance test as promised: -

SET NOCOUNT ON;

SELECT TOP 1000000 Student_ID,

Sex = CASE WHEN sex = 1 THEN 'M' ELSE 'F' END,

RaceCode = CASE race WHEN 0 THEN 'Other' WHEN 1 THEN 'Black' ELSE 'White' END,

Lunch = lunch, Student_ESL = esl, GIEP = giep, IEP = iep,

SchoolCode = schoolcode

INTO #temp

FROM master.dbo.syscolumns sc1

CROSS JOIN master.dbo.syscolumns sc2

CROSS JOIN master.dbo.syscolumns sc3

CROSS APPLY (SELECT DISTINCT LEFT('0' + (CAST((ABS(CHECKSUM(NEWID())) % 999999999) + 1 AS VARCHAR(9))),9)) sc4(Student_ID)

CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc5(sex)

CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 3)) sc6(race)

CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc7(lunch)

CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc8(esl)

CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc9(giep)

CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc10(iep)

CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 899) + 100) sc11(schoolcode);

CREATE CLUSTERED INDEX PK_Student_ID_temp ON #temp (Student_ID);

CREATE NONCLUSTERED INDEX NC_SchoolCode_temp ON #temp (SchoolCode);

PRINT REPLICATE('=',80);

PRINT 'DWAIN';

PRINT REPLICATE('=',80);

SET STATISTICS IO, TIME ON;

;WITH Unpivoted AS (

SELECT SchoolCode, RaceCode, Code, Value

FROM #temp

CROSS APPLY (

VALUES ('LUNCH', Lunch), ('ESL', Student_ESL), ('GIEP', GIEP), ('IEP', IEP)

) a (Code, Value)

)

SELECT Sex=CASE Sex WHEN 'M' THEN 'MALE' ELSE 'FEMALE' END

,BLACK=COUNT(CASE WHEN RaceCode = 'Black' THEN 1 END)

,WHITE=COUNT(CASE WHEN RaceCode = 'White' THEN 1 END)

,OTHER=COUNT(CASE WHEN RaceCode = 'Other' THEN 1 END)

FROM #temp

WHERE SchoolCode = 204

GROUP BY Sex

UNION ALL

SELECT Code

,BLACK=SUM(CASE WHEN RaceCode = 'Black' THEN Value ELSE 0 END)

,WHITE=SUM(CASE WHEN RaceCode = 'White' THEN Value ELSE 0 END)

,OTHER=SUM(CASE WHEN RaceCode = 'Other' THEN Value ELSE 0 END)

FROM Unpivoted

WHERE SchoolCode = 204

GROUP BY Code

SET STATISTICS IO, TIME OFF;

PRINT REPLICATE('=',80);

PRINT 'CADAVRE';

PRINT REPLICATE('=',80);

SET STATISTICS IO, TIME ON;

SELECT Code, BLACK, WHITE, OTHER

FROM (SELECT Code,

BLACK=SUM(CASE WHEN RaceCode = 'Black' AND Value = 1 THEN 1 ELSE 0 END),

WHITE=SUM(CASE WHEN RaceCode = 'White' AND Value = 1 THEN 1 ELSE 0 END),

OTHER=SUM(CASE WHEN RaceCode = 'Other' AND Value = 1 THEN 1 ELSE 0 END),

MAX(Pos) AS Pos

FROM (SELECT SchoolCode, RaceCode, Code, Value, Pos

FROM #temp

CROSS APPLY (VALUES ('LUNCH', Lunch, 5), ('ESL', Student_ESL, 6), ('GIEP', GIEP, 4), ('IEP', IEP, 3),

('Male', CASE WHEN Sex = 'M' THEN 1 ELSE 0 END, 1),

('Female', CASE WHEN Sex = 'F'THEN 1 ELSE 0 END, 2)

) a(Code, Value, Pos)

) a

WHERE SchoolCode = 204

GROUP BY ROLLUP (Code)

) a

WHERE Code IS NOT NULL

ORDER BY Pos;

SET STATISTICS IO, TIME OFF;

Results: -

================================================================================

DWAIN

================================================================================

Sex BLACK WHITE OTHER

------ ----------- ----------- -----------

FEMALE 197 177 171

MALE 164 199 191

LUNCH 178 177 178

GIEP 193 187 179

IEP 184 189 188

ESL 180 186 178

Warning: Null value is eliminated by an aggregate or other SET operation.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#temp'. Scan count 2, logical reads 6762, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 31 ms, elapsed time = 20 ms.

================================================================================

CADAVRE

================================================================================

Code BLACK WHITE OTHER

------ ----------- ----------- -----------

Male 164 199 191

Female 197 177 171

IEP 184 189 188

GIEP 193 187 179

LUNCH 178 177 178

ESL 180 186 178

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#temp'. Scan count 1, logical reads 3381, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 15 ms, elapsed time = 16 ms.

I'm a bit surprised, seems the results are comparable. So I guess it's down to personal preference.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Dwain Camps SSC Guru Points: 86908 More actions · Answer 9

Cadavre (8/8/2012)
Do you not like GROUP BY ROLLUP ?

Actually, I perused the link you provided but couldn't think about how to use ROLLUP that way.

The performance test result is interesting.

Try mine with:

OPTION (MAXDOP 2)

And watch the CPU result level out to yours. I'm still trying to figure that one out.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Cadavre SSC-Forever Points: 41690 More actions · Answer 10

dwain.c (8/8/2012)
Actually, I perused the link you provided but couldn't think about how to use ROLLUP that way.
The performance test result is interesting.
Try mine with:
OPTION (MAXDOP 2)
And watch the CPU result level out to yours. I'm still trying to figure that one out.

In that case, excuse my mis-post 😀

I just had a look at the reads, I've done a useless index in the performance test.

Instead, we'll use these: -

CREATE CLUSTERED INDEX PK_Student_ID_temp ON #temp (Student_ID);

CREATE NONCLUSTERED INDEX NC_SchoolCode_temp ON #temp (SchoolCode) INCLUDE ([Sex],[RaceCode],[Lunch],[Student_ESL],[GIEP],[IEP]);

Full code: -