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

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

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

- Advertisement -