Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2007
    Posts
    41

    Unanswered: T-SQl help needed

    Hi pals,

    Need small help.

    There is a test data with aggregated values from which i need to generated the below report.

    I got stuck over here??? ( whether to use PIVOT / UNPIVOT / anything else).

    Any help would be greatly appreciated.

    Am also attaching the script file.

    Thanks in Advance.

    CREATE TABLE TEST
    (
    District VARCHAR(10),
    School VARCHAR(10),
    Gender VARCHAR(10),
    Race VARCHAR(20),
    [Count] INT
    )
    GO
    DELETE from TEST
    GO
    insert TEST(District,School,Gender,Race,Count) values('D1','S1','M','NULL',90)
    insert TEST(District,School,Gender,Race,Count) values('D1','S1','F','NULL',30)
    insert TEST(District,School,Gender,Race,Count) values('D1','S1','NULL','White',20)
    insert TEST(District,School,Gender,Race,Count) values('D1','S1','NULL','Black',40)
    insert TEST(District,School,Gender,Race,Count) values('D1','S1','NULL','American Indian',50)
    insert TEST(District,School,Gender,Race,Count) values('D1','S1','NULL','Asian American',10)

    insert TEST(District,School,Gender,Race,Count) values('D1','S2','M','NULL',100)
    insert TEST(District,School,Gender,Race,Count) values('D1','S2','F','NULL',20)
    insert TEST(District,School,Gender,Race,Count) values('D1','S2','NULL','White',50)
    insert TEST(District,School,Gender,Race,Count) values('D1','S2','NULL','Black',10)
    insert TEST(District,School,Gender,Race,Count) values('D1','S2','NULL','American Indian',50)
    insert TEST(District,School,Gender,Race,Count) values('D1','S2','NULL','Asian American',10)
    GO

    Report should be displayed as follows

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    DISTRICT| SCHOOL| MALES| FEMALES| Total Students(males+females)| WHITES| BLACK| American Indian | Asian American | Total Non-Whites |Percent Non-White students
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    D1 S1 90 30 90+30 20 40 50 10 (Black+AmericanIndian+AsianAmerican) (Black+AmericanIndian+AsianAmerican)/Total Students*100
    D1 S2 100 20 100+20 50 10 50 10
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2007
    Posts
    183

    Something like this?

    SELECT District, School,
    SUM(CASE WHEN Gender = 'M' THEN 1 ELSE 0 END) AS Males,
    SUM(CASE WHEN Gender = 'F' THEN 1 ELSE 0 END) AS Females,
    COUNT(*) AS Total1,
    SUM(CASE WHEN Gender IS NULL THEN 0 ELSE 1 END) AS Total2,
    SUM(CASE ... THEN ELSE END)
    FROM Table1
    GROUP BY District, School
    ORDER BY District, School
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Peso,
    Something to keep in mind for performance on the sum(case...) stmts. Instead of "then 0" or "else 0" use "then null" or "else null". The nulls do not get added to your sum like the 0 does.
    Dave

  4. #4
    Join Date
    Apr 2007
    Posts
    183
    I know, but yout get a warning when summing NULLs which could be transferred to the client and abort the operation.
    That's why I included "THEN 0" to avoid the warning.
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by dav1mo
    The nulls do not get added to your sum like the 0 does.
    Dave
    Now THAT'S pendantic
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    of such pedantry are performance improvements borne

    what about the difference between

    , SUM(CASE WHEN Gender = 'M' THEN 1 ELSE NULL END)

    and

    , COUNT(CASE WHEN Gender = 'M' THEN 937 ELSE NULL END)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Brett,
    Up to 40% CPU savings is not so pendantic. Of course that depends on how many 0's you may be adding to your sum. Other items along the same lines that have been preached over the years is an identifier in the inner table of a left outer join that denotes if a row exists in the child table. These little things can be the difference between having to purchase bigger/faster hardware and continuing on with what you have in house.
    Dave
    Dave

  8. #8
    Join Date
    Apr 2007
    Posts
    183

    What am I doing wrong here?

    Because I get faster results for the "ELSE 0 END" approach.

    Code:
    CREATE TABLE	#Sample
    		(
    			grpID INT,
    			aValue INT
    		)
    
    INSERT		#Sample
    		(
    			grpID,
    			aValue
    		)
    SELECT		ABS(CHECKSUM(NEWID())) % 200,
    		ABS(CHECKSUM(NEWID())) % 2
    FROM		master..spt_values AS v1
    INNER JOIN	master..spt_values AS v2 ON v2.Type = 'P'
    WHERE		v1.Type = 'P'
    
    CREATE CLUSTERED INDEX IX_Yak ON #Sample (grpID, aValue) -- 4,194,304 records of which about 50% are 0 and 50% are 1 for aValue column
    
    -- SUM zero (1744 ms cpu, 1488 ms duration) ~ 12 executions
    SELECT		grpID,
    		SUM(CASE WHEN aValue = 0 THEN 1 ELSE 0 END)
    FROM		#Sample
    GROUP BY	grpID
    
    -- SUM implicit null (1981 ms cpu, 1667 ms duration) ~ 12 executions
    SELECT		grpID,
    		SUM(CASE WHEN aValue = 0 THEN 1 END)
    FROM		#Sample
    GROUP BY	grpID
    
    -- SUM explicit null (2019 ms cpu, 1656 ms duration) ~ 12 executions
    SELECT		grpID,
    		SUM(CASE WHEN aValue = 0 THEN 1 ELSE NULL END)
    FROM		#Sample
    GROUP BY	grpID
    
    -- COUNT explicit null (1784 ms cpu, 1594 ms duration) ~ 12 executions
    SELECT		grpID,
    		COUNT(CASE WHEN aValue = 0 THEN 937 ELSE NULL END)
    FROM		#Sample
    GROUP BY	grpID
    Last edited by Peso; 07-21-09 at 18:14.
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    COUNT 937 rulez!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Sep 2009
    Location
    Dallas, TX
    Posts
    6
    What's missing is the using COUNT without an ELSE:

    SELECT grpID,
    COUNT(CASE WHEN aValue = 0 THEN 937 END)
    FROM #Sample
    GROUP BY grpID

    For me, I was within 10ms either way of when I used the ELSE NULL with it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •