Subquery SUM issues - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Subquery SUM issues

Author

Topic

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2015-04-16 : 16:41:17

The columns a and b are not summing properly. I need to calculate the total of those and when breaking down the query at least in subquery b the totals are not correct.


SELECT  MAX(msDropRateTotal) AS msDrop ,
        MAX(Hispanic) AS Hispanic ,
        MAX(White) AS white ,
        MAX(Male) AS Male ,
        MAX(Female) AS Female

FROM    ( SELECT  
							  CAST(ISNULL(ROUND(CAST(b AS FLOAT)
                                      / CAST(NULLIF(a, 0) AS FLOAT), 2) ,
                                0) AS VARCHAR) + '%' AS msDropRateTotal ,
                    CASE WHEN ETHNIC = 1
                         THEN CAST(ISNULL(ROUND(CAST(b AS FLOAT)
                                                / CAST(NULLIF(a, 0) AS FLOAT),
                                                2) , 0) AS VARCHAR) + '%'
                    END AS Hispanic ,
                    CASE WHEN ETHNIC = 6
                         THEN CAST(ISNULL(ROUND(CAST(b AS FLOAT)
                                                / CAST(NULLIF(a, 0) AS FLOAT),
                                                2) , 0) AS VARCHAR) + '%'
                    END AS White ,
                    CASE WHEN ETHNIC = 9
                         THEN CAST(ISNULL(ROUND(CAST(b AS FLOAT)
                                                / CAST(NULLIF(a, 0) AS FLOAT),
                                                2) , 0) AS VARCHAR) + '%'
                    END AS Male ,
                    CASE WHEN GENDER = 'F'
                         THEN CAST(ISNULL(ROUND(CAST(b AS FLOAT)
                                                / CAST(NULLIF(a, 0) AS FLOAT),
                                                2) , 0) AS VARCHAR) + '%'
                    END AS Female
          FROM      ( SELECT    ETHNIC ,
                                GENDER ,
                                SUM(E7 + E8 ) AS a ,
                                SUM(d7 + d8 )AS b
                      FROM      dropouts
                      WHERE     ( CDS_CODE LIKE CAST('30666210000000'  AS VARCHAR(7)) + '%' )
                      GROUP BY  ETHNIC ,
                                GENDER 
                    ) AS b
                                       
        ) AS c

Cartesian Yak

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-16 : 17:37:03

Run the subquery by itself and see if you are getting the right numbers. If you are not, one possibility might be that E7, E8, D7 or D8 might have null values. To get around that, change the inner query to

SELECT  ETHNIC ,
        GENDER ,
        SUM(ISNULL(E7,0) + ISNULL(E8,0)) AS a ,
        SUM(ISNULL(d7,0) + ISNULL(d8,0)) AS b
FROM    dropouts
WHERE   ( CDS_CODE LIKE CAST('30666210000000' AS VARCHAR(7)) + '%' )
GROUP BY ETHNIC ,
        GENDER

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2015-04-16 : 17:59:54

its forcing me to do a group by but I don't want that, I just want the one record


SELECT    ISNULL(ROUND(CAST(SUM(D7 + D8) AS FLOAT)
                                      / CAST(NULLIF(SUM(E7 + E8), 0) AS FLOAT), 2),
                                0)  AS msDropRate
                                 ,
                    CASE WHEN ETHNIC = 1
                         THEN ISNULL(ROUND(CAST(SUM(D7 + D8) AS FLOAT)
                                                / CAST(NULLIF(SUM(E7 + E8), 0) AS FLOAT),
                                                2), 0) 
                    END AS Indian ,
                    CASE WHEN ETHNIC = 2
                         THEN ISNULL(ROUND(CAST(SUM(D7 + D8) AS FLOAT)
                                                / CAST(NULLIF(SUM(E7 + E8), 0) AS FLOAT),
                                                2), 0) 
                    END AS Asian ,
                    CASE WHEN ETHNIC = 3
                         THEN ISNULL(ROUND(CAST(SUM(D7 + D8) AS FLOAT)
                                                / CAST(NULLIF(SUM(E7 + E8), 0) AS FLOAT),
                                                2), 0) 
                    END AS Islander ,
                    CASE WHEN ETHNIC = 4
                         THEN ISNULL(ROUND(CAST(SUM(D7 + D8) AS FLOAT)
                                                / CAST(NULLIF(SUM(E7 + E8), 0) AS FLOAT),
                                                2), 0) 
                    END AS Flip ,
                    CASE WHEN ETHNIC = 5
                         THEN ISNULL(ROUND(CAST(SUM(D7 + D8) AS FLOAT)
                                                / CAST(NULLIF(SUM(E7 + E8), 0) AS FLOAT),
                                                2), 0) 
                    END AS Hispanic ,
                    CASE WHEN ETHNIC = 6
                         THEN ISNULL(ROUND(CAST(SUM(D7 + D8) AS FLOAT)
                                                / CAST(NULLIF(SUM(E7 + E8), 0) AS FLOAT),
                                                2), 0) 
                    END AS AA ,
                    CASE WHEN ETHNIC = 7
                         THEN ISNULL(ROUND(CAST(SUM(D7 + D8) AS FLOAT)
                                                / CAST(NULLIF(SUM(E7 + E8), 0) AS FLOAT),
                                                2), 0) 
                    END AS White ,
                    CASE WHEN ETHNIC = 9
                         THEN ISNULL(ROUND(CAST(SUM(D7 + D8) AS FLOAT)
                                                / CAST(NULLIF(SUM(E7 + E8), 0) AS FLOAT),
                                                2), 0) 
                    END AS Two ,
                    CASE WHEN GENDER = 'M'
                         THEN ISNULL(ROUND(CAST(SUM(D7 + D8) AS FLOAT)
                                                / CAST(NULLIF(SUM(E7 + E8), 0) AS FLOAT),
                                                2), 0) 
                    END AS Male ,
                    CASE WHEN GENDER = 'F'
                         THEN ISNULL(ROUND(CAST(SUM(D7 + D8) AS FLOAT)
                                                / CAST(NULLIF(SUM(E7 + E8), 0) AS FLOAT),
                                                2), 0) 
                    END AS Female
                    
                    FROM
                    dropouts
                      WHERE     ( CDS_CODE LIKE CAST('19647740000000'  AS VARCHAR(7)) + '%' )

Cartesian Yak

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2015-04-17 : 14:41:52

This what I had to do, not sure if there was an easier way??


WITH overall (Total)
AS (
 SELECT 
        ISNULL(CAST(SUM(D7 + D8) AS FLOAT)
               / CAST(NULLIF(SUM(E7 + E8), 0) AS FLOAT), 0) AS Total
 FROM   dropouts
 WHERE   ( CDS_CODE LIKE CAST('30666210000000' AS VARCHAR(7)) + '%' )
 ), 
 indian  (Total)
 AS (
 SELECT 
        ISNULL(CAST(SUM(D7 + D8) AS FLOAT)
               / CAST(NULLIF(SUM(E7 + E8), 0) AS FLOAT), 0) AS Total
 FROM   dropouts
 WHERE   ( CDS_CODE LIKE CAST('30666210000000' AS VARCHAR(7)) + '%' ) AND ETHNIC = 1
 ),
 Asian (Total)
 AS
 (
 SELECT 
        ISNULL(CAST(SUM(D7 + D8) AS FLOAT)
               / CAST(NULLIF(SUM(E7 + E8), 0) AS FLOAT), 0) AS Total
 FROM   dropouts
 WHERE   ( CDS_CODE LIKE CAST('30666210000000' AS VARCHAR(7)) + '%' ) AND ETHNIC = 2
 ),
 Islander (Total)
 AS
 (
  SELECT 
        ISNULL(CAST(SUM(D7 + D8) AS FLOAT)
               / CAST(NULLIF(SUM(E7 + E8), 0) AS FLOAT), 0) AS Total
 FROM   dropouts
 WHERE   ( CDS_CODE LIKE CAST('30666210000000' AS VARCHAR(7)) + '%' ) AND ETHNIC = 3
 )
                               
SELECT overall.Total AS overall, indian.Total AS indian, asian.Total AS asian, islander.Total AS islander
FROM overall 

CROSS JOIN 
indian
CROSS JOIN
asian 
CROSS JOIN
islander

Cartesian Yak

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources