Using COUNT(*) within INNER JOIN 1

[jimoo]

This thread references thread183-1305249 which is now closed. It asked for help with an inner join and count. I am also looking for assistance.

Here is the original Thread Reponse that solved the problem and below is my code this is using this as an example, but not working.

bborissov (Programmer) 24 Nov 06 2:52  
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:

CODE
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

My code below - I am trying to get the number of permits for each permitType

select dbo.m_permitType.permittypeid, m_permitType.permitName from dbo.m_permitType 
inner join   (select dbo.permit.permitTypeId, COUNT(*) as PermitCount 
FROM dbo.permit GROUP BY dbo.permit.permitTypeId) ON
dbo.Permit.permittypeid = dbo.m_permitType.permittypeid

I encounter the following error:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'ON'.


Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[gmmastros]

This code uses a derived table technique to get the data. When you use a derived table, you MUST give it an alias, like this.

[tt][blue]
select dbo.m_permitType.permittypeid, m_permitType.permitName from dbo.m_permitType
inner join (select dbo.permit.permitTypeId, COUNT(*) as PermitCount
FROM dbo.permit GROUP BY dbo.permit.permitTypeId) [!]As AliasName[/!] ON
dbo.Permit.permittypeid = dbo.m_permitType.permittypeid
[/blue][/tt]

One other thing, though. When you use an alias for a derived table, you must use that alias name everywhere else in the query, too. Like this:

select dbo.m_permitType.permittypeid,  
       m_permitType.permitName[!],
       PermitCounts.PermitCount[/!]
from   dbo.m_permitType
       inner join (
         select dbo.permit.permitTypeId, 
                COUNT(*) as PermitCount
         FROM   dbo.permit 
         GROUP BY dbo.permit.permitTypeId
         ) [!]As PermitCounts[/!] 
         ON [!]PermitCounts[/!].permittypeid = dbo.m_permitType.permittypeid

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jimoo]

PERFECT!!!

The funny part is I normally use the alias but decided not to this time to rule out an error with my alias names. I thought I would take it in small steps but it turns out I needed the alias this time.

Thanks for taking the time.


Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com