Conditionally Using a Sum - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Conditionally Using a Sum

Author

Topic

masu001
Starting Member

3 Posts

Posted - 2009-07-23 : 04:01:00

Hi All,

I have encountered a problem and cannot seem to find a solution.

I have two tables:
The first contains records for students and corresponding Certifciates they hold

ID Student CertID
1 John 1
2 John 2
3 Tom 2
4 Tom 3
5 John 3

The second contains 1 record for each certificate type, and the "weight" it holds when rating students.

CertTypeID CertTypeName Score
1 ECDL 45
2 ECDL Advanced 90
3 Java Course 10

The Query: The query I am trying to write should sum the total certificate score for each student but with one condition. Certificate Types 1 and 2 should be either or. If a student has the second, then CertType 1 shouldnt be added to his score. In the case of the 3 certificate, this should be added if found in either case.

I've tried various combination of Case Statements but I did not manage to eliminate the "1" when "2" was present.

SELECT SUM(CASE
WHEN (CertTypeID = 1) THEN T3.Score
WHEN (CertTypeID = 3) THEN T3.Score
ELSE (CASE
WHEN (CertTypeID = 2) THEN T3.Score
WHEN (CertTypeID = 3) THEN T3.Score
END )
END)
FROM tblCerts, tblCertType
WHERE CertID = CertTypeID
AND Student = 'John'

In the above example I'm getting the sum of all three, and it makes sense because for each record I have it is either 1, 2 or 3 so all case conditions are visited one way or another.

I don't know if I'm missing something simple, or if my tables can be re-designed.

Any and all help is welcome
Regards
masu

rickh
Starting Member

2 Posts

Posted - 2009-07-23 : 04:14:44

You will have to create a sub query to pull out those with both. I have not tested this but it should provide an idea tof what I mean.

SELECT
SUM(T2.Score)
FROM
tblCerts T1
INNER JOIN tblCertType T2
ON T1.CertID = T2.CertTypeID
LEFT JOIN (SELECT Student, max(CertID) AS CertID
FROM tblCerts
WHERE CertID < 3
GROUP BY Student) T3
ON T3.Student = t1.Student
AND T3.CertID = T1.CertID
WHERE T1.Student = 'John'
AND (T1.CertID = 3 or T3.CertID IS NOT NULL)

masu001
Starting Member

3 Posts

Posted - 2009-07-23 : 04:37:32

Awesome my friend! MS Sql Server Management Studio designer(which I don't like to use very often) actually corrected it by itself, works perfectly!

Thanks alot

masu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-23 : 05:03:41

Instead of hardwiring the business logic in the query, add two columns to your Certifications table.
One column Grp to hold together certifications in group. And one column denoting ranking within group.

The the query can look like this

DECLARE	@Tests TABLE
	(
		ID INT,
		Student VARCHAR(20),
		CertID INT
	)

INSERT	@Tests
SELECT	6, 'Peso', 1 UNION ALL
SELECT	7, 'Peso', 3 UNION ALL
SELECT	1, 'John', 1 UNION ALL
SELECT	2, 'John', 2 UNION ALL
SELECT	3, 'Tom', 2 UNION ALL
SELECT	4, 'Tom', 3 UNION ALL
SELECT	5, 'John', 3

DECLARE	@Certs TABLE
	(
		CertTypeID INT,
		CertTypeName VARCHAR(20),
		Score INT,
		Grp INT,
		Rank INT
	)

INSERT	@Certs
SELECT	1, 'ECDL', 45, 1, 2 UNION ALL
SELECT	2, 'ECDL Advanced', 90, 1, 1 UNION ALL
SELECT	3, 'Java Course', 10, 2, 1

SELECT		Student,
		SUM(Score) AS TotalScore
FROM		(
			SELECT		t.Student,
					c.Score,
					ROW_NUMBER() OVER (PARTITION BY t.Student, c.Grp ORDER BY c.RANK) AS recID
			FROM		@Tests AS t
			INNER JOIN	@Certs AS c ON c.CertTypeID = t.CertID
		) AS d
WHERE		recID = 1
GROUP BY	Student
ORDER BY	Student

N 56°04'39.26"
E 12°55'05.63"

masu001
Starting Member

3 Posts

Posted - 2009-07-23 : 06:44:59

This is a very nice and elegant solution which promises to be a little more sustainable in the long run,

many thanks Peso,

masu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-23 : 07:09:58

You can substitute ROW_NUMBER() with RANK() if there can be two certs in same group with same ranking/priority.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources