SQL Amateur - Need Help - 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 2008 Forums

Transact-SQL (2008)

SQL Amateur - Need Help

Author

Topic

smugg55
Starting Member

4 Posts

Posted - 2012-08-03 : 12:27:59

Hey all,

I'm fairly new to SQL so I need some help. Basically what I'd like to do is pull all of the enrollee IDs (enrolid) from table 1, sum the payments (pay) from table 2 by enrolid in the second column, sum the payments (pay) from table 3 by enrolid in the third column, and sum the payments (pay) by enrolid from table 4 in the fourth column. I wrote some code, but it only pulls the enrolid where there are matching enrolids on tables 2, 3, or 4. I need EVERY enrolid from table 1, regardless of whether they're on tables 2, 3, or 4. When the enrolid is not on tables 2,3, or 4, I need the payments to show up as 0.

Any help would be greatly appreciated, and thanks in advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-03 : 12:36:26

Instead of inner joins you should use left joins - something like this:

SELECT
	t1.enrolid,
	COALESCE(SUM(t2.pay),0) AS T2Pay,
	COALESCE(SUM(t3.pay),0) AS T3Pay,
	COALESCE(SUM(t4.pay),0) AS T4Pay
FROM
	Table1 t1
	LEFT JOIN Table2 t2 ON t1.enrolid = t2.enrolid
	LEFT JOIN Table3 t3 ON t1.enrolid = t3.enrolid
	LEFT JOIN Table4 t4 ON t1.enrolid = t4.enrolid
GROUP BY
	t1.enrolid
ORDER BY
	t1.enrolid

smugg55
Starting Member

4 Posts

Posted - 2012-08-03 : 14:51:55

That code works, but I get the following error: Arithmetic overflow error converting expression to data type int. How do I correct for this error?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-03 : 14:55:01

What is the data type of the pay column in each of these tables? If it is int, you can try casting those as BIGINT, but if you are paying out in BIGINT, then there has to be something wrong in the calculations or data.

...
         COALESCE(SUM(CAST(t2.pay AS BIGINT)),0) AS T2Pay,
...

smugg55
Starting Member

4 Posts

Posted - 2012-08-03 : 16:03:45

I tried casting those as BIGINT, and I got the exact same error. I'm assuming that means there's something wrong in the data. Is there a work-around for problematic data like this? Note that it is an extremely large dataset, so if I have to exclude a few problematic rows, that's not an issue.

smugg55
Starting Member

4 Posts

Posted - 2012-08-03 : 16:06:04

I should also note that the following was part of the error:

Warning: Null value is eliminated by an aggregate or other SET operation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources