Aggregate Count also need o

sizemore · November 19th, 2004, 12:19 AM

I have 2 tables on a lyric discussion site

[u]Titles</u>
   title_id
   title_name
   title_lyricist
   title_lyrics
   title_artist
   title_entered

And [u]POSTS</u>
   post_id
   title_id
   post_author
   post_text

I want to be able to list all the titles, and count the # of posts for each title. The problem is some titles have zero posts so my query ignores thos titles, butI syill want them returnd where Ill add a ')' for count.

This is the best i could come up with, but still only those that have posts are returned. I still want the titles that don't have a match in the posts table to be returned with a total_count of zero.

Any any help on this ASAP would be much appreciated.

<a href='www.flyingroman.com'>Flying Roman lyric meanings</a>

shahchi1 · November 19th, 2004, 03:31 PM

Please post your Query.

Not sure what exactly you are trying to achieve but try this
****
SELECT Titles.title_name, Count(Posts.post_id) AS TotalPosts
FROM Titles LEFT JOIN POSTS ON Titles.title_id = Posts.title_id
GROUP BY Titles.title_name;

****

jemacc · November 22nd, 2004, 04:28 PM

You also try it like this

Code:

select A.title_id
     , A.Title_name
     , count(B.title_ID) as Total_Post
 from titles A
left outer
  join posts B
    on A.Title_id = B.Title_ID
group
    by A.TitleID
     , A.Title_name

Jaime E. Maccou
Applications Analyst