Sql query issue

Hello every one,

I have 2 tables reports and categories
and I'm trying to get this code works,

*select c.cat_name, max(c.crime_no) from reports r, categories c, *
(select c.cat_id, count(r.cat_id) as Crime_No
from reports r, categories c)
where c.cat_id = r.cat_id
group by c.cat_name

I'm not sure where's the error

the error is all of it. You are using deprecated syntax along with invalid sql. There are no joins or an explanation as to what you are trying to do. Looks like cat_id might be primary key to categories table and is also in reports table, but we don't have ddl. I'll take a guess and assume cat_id to be pk

select c.cat_name, count(r.cat_id) as Crime_No
from reports r
join categories c
on r.cat_id = c.cat_id
group by c.cat_name
order by 2 desc

1 Like

thanks mike for the help,,
it worked fine but not quit as I wish
I needed to select only the max value on count(r.cat_id),
anyway, I think this will be good for me,,
thanks again

Hi again,
could you please tell me how to select only the max value on the query?
I tried to make a sub select on this but no luck
SELECT MAX (cat_id) FROM reports