Using COUNT(*) within a INNER JOIN 1

[ShawnMolloy]

Why does this query not work?

select A.*, c.count(*) views, B.Username from video a
inner join user b on a.userid = b.userid
inner join video_view c on a.videoid = c.videoid

It complains:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '*'.

I also tried c.count(videoid) and count(c.videoid)... neither worked.

Thanks,



Shawn Molloy
Seattle, WA

 

[bborissov]

You can't use c.count(*), the correct syntax is count(c.SomeField). Also when you have COUNT(), SUM() etc. you must have GROUP BY. But when you have GROUP BY you must include ALL fields which are not involved in agregate functions. So beeter try:

select A.*,
       c.views,
       B.Username
from video a
inner join user b on a.userid = b.userid
inner join (SELECT videoid, COUNT(*) AS Views
                   FROM video_view
                   GROUP BY videoid)c
      on a.videoid = c.videoid

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ShawnMolloy]

Thank you Borislav. Works great!

Shawn Molloy
Seattle, WA