Inner join and group by So I've got two tables. 

Accounts has three columns:

id, Account_ID, Account_Name


Tickets also has three columns:

id, Account_ID, Description


I can use the following query to get the most commonly occurring account IDs from within the ticket table - The accounts which are the most active in submitting tickets

select account_id, count(*) as 'occurences' from tickets group by account_id order by occurences desc


which is great, but i'd like to group that on the accounts table so the output reads

Accounts.Account_Name, Count of occurences from previous query


I tried this codeplex example to no avail, I get the error

The multi-part identifier "ticket.Account_ID" could not be bound.


using this query

Select accounts.Account_Name, Accounts.Account_ID, TotalTickets
from Accounts  
inner join 
(Select tickets.Account_ID,count(*) as TotalTickets
from  Tickets group by ticket.Account_ID) acctTickets 
on acctTickets.Account_ID= accounts.Account_ID


Thanks!
Try:

select ac.Account_Name, ac.Account_ID, count(t.ID) as 'Total Tickets'
from accounts ac
join tickets t on
ac.Account_ID = t.Account_ID
group by ac.account_ID, ac.account_name


I think the only error in your query is that you aliased "tickets" as "ticket", but your query is also more complex than it needs to be.