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)
 Help With Query Please
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

skylimitsql
Starting Member

8 Posts

Posted - 08/10/2014 :  06:28:15  Show Profile  Reply with Quote
Hi,
Partially I have been able get the result but not desired result,
please help me with this.

I have these two table

declare @T1 Table(id1 int, Name Varchar(90))
insert into @T1
select 1, 'Steve' union all
select 2, 'Joe' union all
select 3, 'Bob' union all
select 4, 'Julie' union all
select 5, 'Harry' union all
select 6, 'Fred' union all
select 7, 'Jill'


declare @T2 Table(score int, id2 int)
insert into @T2
select 300, 4 union all
select 120, 6 union all
select 12, 3 union all
select 140, 6 union all
select 543, 1 union all
select 455, 2 union all
select 311, 3 union all
select 555, 6 union all
select 234, 5

A SQL query to show all the names in the T1 table that do not have cumulative scores of at least 450 (note that Jill should be included in such a list as she has taken no tests at all and therefore not scored!)

So far I can fire this query
select Name, TotalScore from @T1 t1 left join
(
select id2, SUM(score)TotalScore from @T2 group by id2 --having SUM(score) < 450

)d

on t1.id1 = d.id2 and TotalScore < 450

Result
Name TotalScore
Steve NULL
Joe NULL
Bob 323
Julie 300
Harry 234
Fred NULL
Jill NULL

but the correct result should have been

Bob 323
Julie 300
Harry 234
Jill NULL

Please help me to write this query

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/10/2014 :  08:00:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- SwePeso
SELECT		t1.name,
		SUM(t2.score)
FROM		@t1 AS t1
LEFT JOIN	@t2 AS t2 ON t2.id2 = t1.id1
GROUP BY	t1.name,
		t1.id1
HAVING		SUM(ISNULL(t2.score, 0)) < 450;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1510 Posts

Posted - 08/10/2014 :  08:00:49  Show Profile  Reply with Quote
Why do you want Jill but not steve? Both have null sums.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/10/2014 :  08:53:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
gbritton, check the sample data. OP expected output is flawed.
Steve has 543 for score.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

skylimitsql
Starting Member

8 Posts

Posted - 08/10/2014 :  08:57:58  Show Profile  Reply with Quote
Many Thanks SwePeso. This is exactly what I wanted. Appreciate that.
Regards
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1510 Posts

Posted - 08/10/2014 :  09:18:05  Show Profile  Reply with Quote
quote:
Originally posted by skylimitsql

Many Thanks SwePeso. This is exactly what I wanted. Appreciate that.
Regards



Well the subquery returns no rows for steve since his score is too high.
Then the left join gives him null correctly. So my question still applies.
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.06 seconds. Powered By: Snitz Forums 2000