COUNT Problem - 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 2005 Forums

Transact-SQL (2005)

COUNT Problem

Author

Topic

fabiosa
Starting Member

3 Posts

Posted - 2007-03-15 : 11:41:41

Hi,

I am quite new to T-SQL and I have created a PROC to return tables. However, I am having a problem with COUNT in this statement:


SELECT [InjuryID], [AccidentID], [Injury_Area], [Injury_Severity], [Injury_Description],
			COUNT (InjuryID) AS InjuryCount 
			FROM [Accident Reports].[dbo].[Injuries]
			WHERE AccidentID=@Accident_ID
			GROUP BY [InjuryID], [AccidentID], [Injury_Area], [Injury_Severity], [Injury_Description]
			ORDER BY InjuryID

When I have say 3 injuries which match the query, it returns all 3 rows but the InjuryCount returns as 1 instead of 3, is there something wrong with my query?

Thanks for any help.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-03-15 : 11:51:49

You're grouping by all the Injury information, so will only ever get a count of 1 per InjuryID. I'm assuming you want a count of injuries by accident, in which case you'll need to remove all the injury fields from your select and group by.

Mark

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 11:52:48

quote:
SELECT [InjuryID], [AccidentID], [Injury_Area], [Injury_Severity], [Injury_Description], COUNT(*) AS InjuryCount
FROM [Accident Reports].[dbo].[Injuries]
WHERE AccidentID = @Accident_ID
GROUP BY [InjuryID], [AccidentID], [Injury_Area], [Injury_Severity], [Injury_Description]
ORDER BY InjuryID

You group by almost all fields!
Maybe all you need is this

SELECT [InjuryID], COUNT(*) AS InjuryCount 
FROM [Accident Reports].[dbo].[Injuries]
WHERE AccidentID = @Accident_ID
GROUP BY [InjuryID]
ORDER BY [InjuryID]

Peter Larsson
Helsingborg, Sweden

fabiosa
Starting Member

3 Posts

Posted - 2007-03-15 : 11:57:37

quote:
Originally posted by Peso

quote:
SELECT [InjuryID], [AccidentID], [Injury_Area], [Injury_Severity], [Injury_Description], COUNT(*) AS InjuryCount
FROM [Accident Reports].[dbo].[Injuries]
WHERE AccidentID = @Accident_ID
GROUP BY [InjuryID], [AccidentID], [Injury_Area], [Injury_Severity], [Injury_Description]
ORDER BY InjuryID
You group by almost all fields!
Maybe all you need is this
SELECT [InjuryID], COUNT(*) AS InjuryCount
FROM [Accident Reports].[dbo].[Injuries]
WHERE AccidentID = @Accident_ID
GROUP BY [InjuryID]
ORDER BY [InjuryID]

Peter Larsson
Helsingborg, Sweden

Thanks for this, how would I reintegrate the remainder of the info back into the select statement?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 12:02:01

Did the query above produce the result you need?
Are all injuries there?
[code]
SELECT i.InjuryID, @Accident_ID AS AccidentID, i.Injury_Area, i.Injury_Severity, i.Injury_Description, x.InjuryCount
from [Accident Reports].dbo.Injuries AS i
inner join (
SELECT [InjuryID], COUNT(*) AS InjuryCount
FROM [Accident Reports].[dbo].[Injuries]
WHERE AccidentID = @Accident_ID
GROUP BY [InjuryID]) AS x on x.InjuryID = i.InjuryID

Peter Larsson
Helsingborg, Sweden

fabiosa
Starting Member

3 Posts

Posted - 2007-03-15 : 12:12:27

quote:

Originally posted by Peso

Did the query above produce the result you need?
Are all injuries there?


SELECT i.InjuryID, @Accident_ID AS AccidentID, i.Injury_Area, i.Injury_Severity, i.Injury_Description, x.InjuryCount 
from [Accident Reports].dbo.Injuries AS i
inner join (
SELECT [InjuryID], COUNT(*) AS InjuryCount 
FROM [Accident Reports].[dbo].[Injuries]
WHERE AccidentID = @Accident_ID
GROUP BY [InjuryID]) AS x on x.InjuryID = i.InjuryID


Peter Larsson
Helsingborg, Sweden

Thanks Peter, I changed it to this:

[code]
SELECT i.InjuryID, @Accident_ID AS AccidentID, i.Injury_Area, i.Injury_Severity, i.Injury_Description, x.InjuryCount
from Injuries AS i
inner join (
SELECT [AccidentID], COUNT(*) AS InjuryCount
FROM [Accident Reports].[dbo].[Injuries]
WHERE AccidentID = @Accident_ID
GROUP BY [AccidentID]) AS x on x.AccidentID = i.AccidentID

as the AccidentID is the foreign key I was acting on and it did the trick. Looks like I've got a lot to learn.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources