Aggregate Function - 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

Aggregate Function

Author

Topic

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-12-15 : 14:52:58

I have a two tables joined via an outer join.

First table
cID,Title

second Table
ccID,cID,Comments

Select
c.ID,
c.Title,
count(cc.ccID) commentCount
from Content c
left outer join ContentComments cc
on
c.cID =cc.cID
tried something like the above not working.
I need to get the content and how many comments are there on each content
How can I do this?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-15 : 14:57:38

quote:
Originally posted by BendJoe

I have a two tables joined via an outer join.

First table
cID,Title

second Table
ccID,cID,Comments

Select
c.ID,
c.Title,
count(cc.ccID) commentCount
from Content c
inner join ContentComments cc
on c.cID =cc.cID
group by c.ID,c.Title

tried something like the above not working.
I need to get the content and how many comments are there on each content
How can I do this?

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-12-15 : 15:11:41

isnull(count(cc.ccID),0)
How could I get a value 0 if the count is null

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-15 : 15:43:53

quote:
Originally posted by BendJoe

I have a two tables joined via an outer join.

First table
cID,Title

second Table
ccID,cID,Comments

Select
c.ID,
c.Title,
isnull(count(cc.ccID),0) commentCount
from Content c
left outer join ContentComments cc
on
c.cID =cc.cID
group by c.ID,c.Title
tried something like the above not working.
I need to get the content and how many comments are there on each content
How can I do this?

genius_palli
Starting Member

42 Posts

Posted - 2008-12-22 : 00:49:31

Hi Everyone ...
I have the following Query in MS Access and i need to convert it to SQL Query.
* ---------------------- *
TRANSFORM First(IIf([ELine],"E Line",[Description] & " "+Format([time],"h:nn am/pm")) & IIf(IsNull([note]),"",Chr(13)+Chr(10)+[note])) AS Expr1
SELECT Avails.[People ID]
FROM [Avails Status] INNER JOIN Avails ON [Avails Status].Code = Avails.Status
WHERE (((Avails.Date) Between [forms]![reports form]![DD1] And DateAdd("d",6,[forms]![reports form]![DD1])))
GROUP BY Avails.[People ID]
PIVOT Format([Date],"ddd") In ("Fri","Sat","Sun","Mon","Tue","Wed","Thu");
* ---------------------------- *

When i try to execute this query in sql , i am not getting the right aggregate function to use in place of first.
please help... thanks in advance for your priceless suggestions.

----------------------------------
Every experience has something to learn. Go get it.
Thanks and Regards
genius_palli
Web Developer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources