Help With Query Please - 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)

Help With Query Please

Author

Topic

skylimitsql
Starting Member

8 Posts

Posted - 2014-08-10 : 06:28:15

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

30421 Posts

Posted - 2014-08-10 : 08:00:29

[code]-- 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;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA