Inner join and group by
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.