Post row data even if record not found

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)

Post row data even if record not found

Author

Topic

ddobbertin
Yak Posting Veteran

51 Posts

Posted - 2007-04-06 : 15:41:07

I know...sounds confusing, but it's not.

I have a table in this format (underscores are just place holders for the forum):

Shift - Points
1_______5
1_______4
3_______9

There are 4 work shifts 1-4, I need to get the average of points per shift. Simple query yes I know...however if there has not been data posted for a shift I still want the return to show the shift number and 0 for the average. So using the above table I would return the following:

Shift - Average Points
1_______4.5
2_______0.0
3_______9
4_______0.0

Any and all help is appreciated!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-06 : 15:53:14

All you need is a left outer join, assuming you have a table of Shifts. If you don't have a table of Shifts, then you need one that defines shifts.

If you have that, then all you need is something like this:

select shifts.shift, isnull(avg(ShiftPoints.Points), 0) as AvgShiftPoints
from shifts
left outer join
shiftPoints on shifts.shift = shiftPoints.shift
group by shifts.shift

- Jeff
http://weblogs.sqlteam.com/JeffS

ddobbertin
Yak Posting Veteran

51 Posts

Posted - 2007-04-06 : 18:39:50

So since I do not have a table of Shifts I should create one that uses it has a lookup table? Then when the join does not find anything that matches it will return the shift and a zero count?

Any way of doing it without having a seperate table...maybe a CASE in the SELECT statement???

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-04-06 : 19:08:56

You can use derived table instead -

select shifts.shift, isnull(avg(ShiftPoints.Points), 0) as AvgShiftPoints
from (
select 1 as shift
union
select 2
union
select 3
union
select 4
) shifts
left outer join
shiftPoints on shifts.shift = shiftPoints.shift
group by shifts.shift

ddobbertin
Yak Posting Veteran

51 Posts

Posted - 2007-04-06 : 19:17:41

Awesome....I think that did it...

Thanks cvraghu....dd

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-06 : 21:14:04

If Shifts are important to your database, you should simply create a shifts table. you can then have referential integrity, you can add any other attributes required to the Shifts table, and you can maintain Shifts simply by editing a table, and not editing your code.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources