Joining tables and sums - 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

General SQL Server Forums

New to SQL Server Programming

Joining tables and sums

Author

Topic

mauisys
Starting Member

7 Posts

Posted - 2012-07-24 : 18:54:17

Hi,

I have the following table structure:

tbl_Subject > one-to-many > tbl_Group > one-to-many > tbl_Item

I would like to know if the following is possible:

tbl_Subject
------------
subjID empID ... other
1 236
2 236
3 542
4 725

tbl_Group
------------
grpID subjID ... other
10 1
20 1
10 2
10 3
10 4

tbl_Item
-------------
itemID grpID subjID isFailed ... other
1 10 1 1
2 10 1 0
3 20 1 0
4 10 2 0
5 10 2 0
6 10 3 1
7 10 3 1
6 10 4 0
8 10 4 0

The result that I am trying to get would look like this
empID countFailed countPassed
236 1 1
542 1 0
725 0 1

So, I want to count one failed if any of the items have a failed value for in the item table, (not all of the failed items).

I am running MS-SQL 2008r2.

Thanks for any help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-24 : 20:49:02

Would this give you the results you are looking for?

SELECT
	s.empId,
	SUM(DISTINCT CASE WHEN i.isFailed = 1 THEN 1 ELSE 0 END),
	SUM(DISTINCT CASE WHEN i.isFailed = 0 THEN 1 ELSE 0 END)
FROM
	tbl_Subject s
	INNER JOIN tbl_Group g ON g.subjId = s.subjId
	INNER JOIN tbl_Item i ON i.grpId = g.grpId AND i.subjId = g.subjId
GROUP BY
	s.empId

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 22:59:44

[code]
SELECT s.empID,
SUM(CASE WHEN Failed = 1 THEN 1 ELSE 0 END) AS CountFailed,
SUM(CASE WHEN Failed = 0 THEN 1 ELSE 0 END) AS CountPassed
FROM tbl_Subject s
INNER JOIN (SELECT subjID,
CASE WHEN SUM(CASE WHEN isFailed =1 THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END AS Failed
FROM tbl_Item i
INNER JOIN tbl_Group g
ON i.grpID = g.grpID
AND i.subjID = g.subjID
GROUP BY subjID
)i
ON i.subjID = s.subjID
GROUP BY s.empID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mauisys
Starting Member

7 Posts

Posted - 2012-07-25 : 12:09:19

Thanks so much visakh16. This is exactly what I needed!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 12:13:09

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources