Count remaining items

Question

I have a database where I store REQUESTS (messages from people), and another where I have CATEGORIES for these queries that can be applied (for example, mark a message).

I have a SQL fragment that looks like this:

select ic.name, count(i.id) as usage_count from inquiry i
inner join inquiry_category ic on ic.id = i.category
group by ic.name
order by usage_count desc

This gives the following results:

Water 6
Electricity 2

... etc. Basically, I get the number of times the query category has been applied.

Now I want the time counter in which it was NOT applied (queries without a heading). How would I add this to the query? BTW, query.category = 0 means that no category has been applied, so it will work with it.

It would also be nice if it always ended as a result of FIRST.

/Robert

Answer 1

LEFT JOIN, UNION

SELECT IF(ic.name IS NOT NULL, ic.name, 'Uncategoried') as name. count(i.id) as usage_count 
 FROM inquiry i
 LEFT JOIN inquiry_category ic ON ic.id = i.category
 GROUP BY ic.name
 ORDER BY usage_count desc

Answer 2

, . :

select 'Uncategorized' as name, count(i.id) as usage_count from inquiry i where i.category = 0

union all

select ic.name, count(i.id) as usage_count from inquiry i
inner join inquiry_category ic on ic.id = i.category
group by ic.name
order by usage_count desc

. !

Answer 3

select CASE WHEN ic.name IS NULL THEN 'Uncategorized' ELSE ic.name END as category_name, count(i.id) as usage_count from inquiry i
leftjoin inquiry_category ic on ic.id = i.category
group by category_name
order by usage_count desc