Group By and Count - 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)

Group By and Count

Author

Topic

NeilC
Yak Posting Veteran

55 Posts

Posted - 2007-12-17 : 08:29:49

Ok I have this query:


declare @RDC varchar(150)
Set @RDC = 9
select O.ClientSurveyID,O.OtherDescription, S.DateRecieved, S.rdcID
from dbo.lf2_ftri_Client_Survey_Other O
left outer join dbo.lf2_ftri_Client_Survey S on S.ClientSurveyID = O.ClientSurveyID

where DateRecieved Between 1/1/2006 and 12/31/2008--@start and @end
and rdcID in (select value from dbo.ParmsToList (@RDC))

which returns the following:
ClientSurveyID OtherDescription DateRecieved rdcID
-------------- -------------------------------------------------- ----------------------- -----------
138 Test 2 2008-12-01 00:00:00 9
138 Test 2 2008-12-01 00:00:00 9
138 Test 3 2008-12-01 00:00:00 9

I need to combine it with somthing like:

select count(OtherDescription) as OtherDescriptionTotal, OtherDescription 
from dbo.lf2_ftri_Client_Survey_Other
group by OtherDescription

to group by OtherDescription and sum them but im not sure how to to this because of the group by clause:

OtherDescriptionTotal OtherDescription
--------------------- --------------------------------------------------
2 Test 2
1 Test 3

Any Ideas?? Your help is always appreciated!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 08:31:47

Where are the table alias prefix for WHERE clause?

E 12°55'05.25"
N 56°04'39.16"

NeilC
Yak Posting Veteran

55 Posts

Posted - 2007-12-17 : 08:33:34

Peso, I don't follow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-17 : 08:34:02

Select t2.OtherDescriptionTotal,t1.Required_columns from
(query1) as t1
inner join
(query2) as t2
on t1.OtherDescription =t2.OtherDescription

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 08:36:11

The columns {DateRecieved, rdcID} don't have corresponding table alias prefix.
In you case, either "O." or "S."

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-17 : 08:36:56

Do like this:-
declare @RDC varchar(150)
Set @RDC = 9
select O.ClientSurveyID,O.OtherDescription,T.OtherDescriptionTotal, S.DateRecieved, S.rdcID
from dbo.lf2_ftri_Client_Survey_Other O
inner join
(select count(OtherDescription) as OtherDescriptionTotal, OtherDescription
from dbo.lf2_ftri_Client_Survey_Other
group by OtherDescription)T
on T.OtherDescription=O.OtherDescription
left outer join dbo.lf2_ftri_Client_Survey S on S.ClientSurveyID = O.ClientSurveyID
where DateRecieved Between 1/1/2006 and 12/31/2008--@start and @end
and rdcID in (select value from dbo.ParmsToList (@RDC))

NeilC
Yak Posting Veteran

55 Posts

Posted - 2007-12-17 : 08:39:54

Nice! Thanks Guys!!

I love SQLTeam :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources