group by records in one table - 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

group by records in one table

Author

Topic

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-07-30 : 12:21:28

Hi all, is there a way to shorten the following code; perhaps by grouping by all the records in the articles table? It works fine but if anyone has any pointers on things that could be done better it would be appreciated.
Thanks in advance.


SELECT
	a.id,cast(a.article_text as varchar(1000)) as article_text,a.article_title,a.article_date,a.article_author,a.article_url,COUNT(c.pageid) as comment_count
FROM
	articles a
	left join comments c on a.id = c.pageid
GROUP BY
	a.id,cast(a.article_text as varchar(1000)),a.article_title,a.article_date,a.article_author,a.article_url
order by
a.article_date desc

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 13:17:36

may be this:-

SELECT
	a.id,cast(a.article_text as varchar(1000)) as article_text,a.article_title,a.article_date,a.article_author,
a.article_url,comment_count
FROM
	articles a
	left join 
(SELECT COUNT(c.pageid) as comment_count,pageid
FROM comments 
GROUP BY pageid)c on a.id = c.pageid
order by
a.article_date desc

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-07-30 : 13:29:33

Thanks, appreciate the reply but should I be trying to avoid using nested select statements where possible?

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 13:32:31

quote:
Originally posted by Cowboy

Thanks, appreciate the reply but should I be trying to avoid using nested select statements where possible?

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

nested select? its not nested select but what i've given you is a derived table.

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-07-30 : 13:43:01

Ok thanks, does it seem logical to do this or am I nitpicking?

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 13:47:41

quote:
Originally posted by Cowboy

Ok thanks, does it seem logical to do this or am I nitpicking?

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

didnt get you. why do you not prefer the given soln?

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-07-30 : 15:24:15

I was curious if there were any performance benefits or deficits. I just tried it and I'm getting:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "c.pageid" could not be bound.

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources