SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Trying to add up columns to produce a single value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kcarbone1970
Starting Member

USA
35 Posts

Posted - 09/21/2013 :  21:27:01  Show Profile  Reply with Quote
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

Edited by - kcarbone1970 on 09/21/2013 22:21:30

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/22/2013 :  02:59:00  Show Profile  Reply with Quote
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
Starting Member

USA
35 Posts

Posted - 09/22/2013 :  12:58:50  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 09/22/2013 :  13:12:16  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000