I'm trying to build a query to look at tickets in our help desk system. I would like to search for a few keywords, ie "email" in either the subject or description fields, and return a count of each keyword. Below is a sample table. There is a categories row as well, however it wasn't well maintained so most of the fields are empty.
ID | Subject | Description
-----+-------------------------------------
1 | hellloooo | can't send email
2 | email down? | can't get to intranet
3 | Phone trouble | can't check voicemail
4 | Using my phone | I don't know how
5 | Need new mouse | please replace
6 | Mouse acting funny | n/a
7 | Intranet won't Load | what's this plug do?
8 | not getting voicemail | why not?
And I would like to return something like
Category | # of tickets
---------------------------
Email | 3
Phone | 2
Intranet | 2
What would the easiest way to do this be?
You did not specify the RDBMS, but the following will work in SQL Server
;with cte as
(
select 'Email' Category
UNION ALL
select 'Phone'
UNION ALL
select 'Intranet'
)
select category, count(*)
from cte c
inner join tickets t
on t.subject like '%' + c.category + '%'
or t.description like '%' + c.category + '%'
group by category
If you have a longer list of categories that you want to query on, then you might want to consider storing it in a separate table and joining on the table.
Edit for Sqlite, there is not CTE so you can do my example the following way (Updated to use the correct syntax to concatenate in SQLite):
select category, count(*)
from
(
select 'Email' Category
UNION ALL
select 'Phone'
UNION ALL
select 'Intranet'
) c
inner join tickets t
on t.subject like '%' || c.category || '%'
or t.description like '%' || c.category || '%'
group by category