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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -