Consolidating Identical Rows - 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

General SQL Server Forums

New to SQL Server Programming

Consolidating Identical Rows

Author

Topic

invisible777
Starting Member

10 Posts

Posted - 2007-12-04 : 12:41:58

I'm using a query to see how many times an action was recorded on a person. The query works, it returns this:

John Smith 1
John Smith 1
John Smith 1
Jane Doh 1
Jane Doh 1
Al Johnson 1

but I need it to return totals like this

John Smith 3
Jane Doh 2
Al Johnson 1

This is the query I am using:


Select Player.First_Name, Player.Last_Name, COUNT(Action.Employee_ID)
from Player INNER JOIN
PlayerVisit on PlayerVisit.Player_ID = Player.Player_ID
join Treatment on Treatment.Visit_ID = PlayerVisit.Visit_ID
join Action on Treatment.Action_ID = Action.Action_ID
group by Player.First_Name, Player.Last_Name, Action.Employee_Id;

gavakie
Posting Yak Master

221 Posts

Posted - 2007-12-04 : 12:44:48

So are the numbers in a different column, try to not put Performs.Employee_Id;
in the group by its not needed

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-04 : 12:45:39

Select Patient.First_Name, Patient.Last_Name, COUNT(Performs.Employee_ID)
...
group by Patient.First_Name, Patient.Last_Name

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

invisible777
Starting Member

10 Posts

Posted - 2007-12-04 : 12:52:11

perfect, worked like a charm, thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-05 : 02:22:34

quote:
Originally posted by invisible777

I'm using a query to see how many times an action was recorded on a person. The query works, it returns this:

John Smith 1
John Smith 1
John Smith 1
Jane Doh 1
Jane Doh 1
Al Johnson 1

but I need it to return totals like this

John Smith 3
Jane Doh 2
Al Johnson 1

This is the query I am using:

Select Player.First_Name, Player.Last_Name, COUNT(Action.Employee_ID)
from Player INNER JOIN
PlayerVisit on PlayerVisit.Player_ID = Player.Player_ID
join Treatment on Treatment.Visit_ID = PlayerVisit.Visit_ID
join Action on Treatment.Action_ID = Action.Action_ID
group by Player.First_Name, Player.Last_Name, Action.Employee_Id;

Grouping by the column that is part of aggregate function doesnt make any sense. It is just like not gouping by it

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources