Trying to add up columns to produce a single value

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)

Trying to add up columns to produce a single value

Author

Topic

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-09-21 : 21:27:01

I have this query that im working on and its not producing accurate results. I need to add up all the 'A' in each column and if its over a certain amount it should produce an answer. However if there are no 'A' it becomes blank and I need it to say 'Yes'. Any help is greatly appreciated..

SELECT CASE WHEN [Total Absenecs] < 9
THEN 'Yes'
         ELSE 'No' END AS [On Target?]    
FROM (


SELECT COUNT(a.absences)  AS [Total Absenecs] FROM 
(
SELECT A1 AS absences FROM ATT INNER JOIN STU ON ATT.SC = STU.SC AND ATT.SN = STU.SN WHERE(ATT.A1 = 'A' AND ATT.A1 is null) AND (STU.ID = 4090368)
UNION ALL
SELECT A2 AS absences FROM ATT INNER JOIN STU ON ATT.SC = STU.SC AND ATT.SN = STU.SN WHERE(ATT.A2 = 'A' AND ATT.A1 is null) AND (STU.ID = 4090368)
UNION ALL
SELECT A3 AS absences FROM ATT INNER JOIN STU ON ATT.SC = STU.SC AND ATT.SN = STU.SN WHERE(ATT.A3 = 'A' AND ATT.A1 is null) AND (STU.ID = 4090368)
UNION ALL
SELECT A4 AS absences FROM ATT INNER JOIN STU ON ATT.SC = STU.SC AND ATT.SN = STU.SN WHERE(ATT.A4 = 'A' AND ATT.A1 is null) AND (STU.ID = 4090368)
UNION ALL
SELECT A5 AS absences FROM ATT INNER JOIN STU ON ATT.SC = STU.SC AND ATT.SN = STU.SN WHERE (ATT.A5 = 'A' AND ATT.A1 is null) AND (STU.ID = 4090368)
UNION ALL
SELECT A6 AS absences FROM ATT INNER JOIN STU ON ATT.SC = STU.SC AND ATT.SN = STU.SN WHERE(ATT.A6 = 'A' AND ATT.A1 is null) AND (STU.ID = 4090368)

 ) 
 a
GROUP BY a.absences
)
AS derived

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 02:59:00

your current condition doesnt make any sense

(ATT.A1 = 'A' AND ATT.A1 is null)

how can same column have a value and be NULL at same time. It will always return nothing

i think what you want is something like


SELECT SUM(CASE WHEN Val = 'A' THEN 1 ELSE 0 END) AS AS [Total Absenecs]
FROM
(
SELECT ATT.*
FROM ATT 
INNER JOIN STU 
ON ATT.SC = STU.SC 
AND ATT.SN = STU.SN 
WHERE STU.ID = 4090368
)t
UNPIVOT (Val FOR Cat IN ([A1],[A2],[A3],[A4],[A5],[A6]))u

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-09-22 : 12:58:50

Right, I was screwing around with so many variations of this thing...Anyway thanks for the query works great

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 13:12:16

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources