Sum columns based on a single column 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

General SQL Server Forums

New to SQL Server Programming

Sum columns based on a single column value

Author

Topic

sis-sql_2015
Starting Member

4 Posts

Posted - 2014-09-10 : 14:59:39

I’m trying to figure out a way to sum columns for similar IDs, based on the contents of a single field. For example, if I’m calculating attendance percentages for students, and codes P and T count as Present, and codes A and E count as Absent, I would want to total Present and Absent codes separately, in their own columns. I would then like to use those totals to calculate percentage, but I can do that. It’s the SUM based on column value (by ID) that is giving me headaches.

If I have the following view:
[CODE]
ID LN FN CD Total
123456 Mears Ben P 12
123456 Mears Ben A 2
123456 Mears Ben T 6
234567 Norton Susan P 15
234567 Norton Susan A 2
234567 Norton Susan T 2
234567 Norton Susan E 1
[/CODE]

I would like something like this:
[CODE]
ID LN FN Present Absent Percentage
123456 Mears Ben 18 2 90.0
234567 Norton Susan 17 3 85.0
[/CODE]

I’ve been playing around with nested queries, but nothing’s working. Any ideas? This is a glimpse of the mess that I’ve created trying to sort this out. Many errors.

EDIT: I just noticed that I used a simpler example than the SQL I included, so I modified it a bit. There are additional fields that I'll need to include, but I want to get the logic working correctly. From there, I can handle the rest. So here's a more appropriate code example showing the direction I'm trying to go with this.

SELECT ID, [Last Name], [First Name], CD, Present, Absent, CAST(LEFT(Present / (Absent + Present) * 100, 5) AS varchar) + '%' AS Percentage
FROM (SELECT ID, CD, TotalAHD, CAST
		((SELECT SUM(TotalAHD) AS Expr1
            FROM SumAHDforAttndPercentages AS p
            WHERE (p.CD = 'P') OR
				  (p.CD = 'T')) AS DECIMAL) AS Present, CAST
		((SELECT SUM(TotalAHD) AS Expr2
			FROM SumAHDforAttndPercentages AS p
			WHERE (p.CD = 'A') OR
				  (p.CD = 'E')) AS DECIMAL) AS Absent
     FROM SumAHDforAttndPercentages
     GROUP BY ID, [Last Name], [First Name])

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-09-10 : 15:30:59

you could probably clean up the calc -

CREATE TABLE #T
(ID int, LN varchar(10), FN varchar(10) ,CD char(1), Total int)

INSERT INTO #T
VALUES
(1123456 ,'Mears' ,'Ben', 'P' ,12)
,(1123456, 'Mears' ,'Ben' ,'A' ,2)
,(1123456, 'Mears' ,'Ben' ,'T' ,6)
,(1234567, 'Norton' ,'Susan', 'P', 15)
,(1234567, 'Norton' ,'Susan', 'A' ,2)
,(1234567, 'Norton' ,'Susan', 'T', 2)
,(1234567, 'Norton' ,'Susan', 'E', 1)

SELECT *, CAST((1- ( [Absent])/([Absent]+ [Present])) * 100 as numeric(4,2))
FROM
(
SELECT ID,LN,FN, CASE WHEN CD IN ('P','T') THEN 'Present' ELSE 'Absent' END Attendence,Total *1.00 Total
FROM #T
) p
PIVOT
(
SUM (Total)
FOR Attendence IN
( [Present], [Absent])
) AS pvt

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-09-10 : 16:15:26

Alternative:

select ID
      ,[Last Name]
      ,[First Name]
      ,sum(case when CD in ('P','T') then TotalAHD else 0 end) as Present
      ,sum(case when CD in ('A','E') then TotalAHD else 0 end) as Absent
      ,sum(case when CD in ('P','T') then TotalAHD else 0 end)/sum(TotalAHD)*100 as Percentage
  from SumAHDforAttndPercentages
 group by ID
         ,[Last Name]
         ,[First Name]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources