David Russell David Russell - 5 months ago 11
SQL Question

How to Display top search terms from a MySQL database and grouped by date

I have a MySQL database that looks a lot like this:



Term-----------LOB---------Date------
dde JM Part A 2016-07-01
drug waste JM Part A 2016-07-01
drug waste JM Part A 2016-07-01
Duplicates JM Part A 2016-07-01
credit balance JM Part B 2016-07-01
drug waste JM Part B 2016-07-01


So, I want the TOP 4 (or n) 'terms' per LOB (lob is a category) - per Month

So in this example it could be


JM Part A July 2016
--dde - 7
--drug waste - 5
--credit balance - 3
--duplicates - 1



JM Part B - July 2016
--dde - 7
--discharge stats- 5
--credit balances- 3
--overpayment - 1


Alas, I am a little lost. The best I am able to do so far is


SELECT term, lob, COUNT(term) uid_count
FROM qs
GROUP BY lob
ORDER BY uid_count DESC
LIMIT 5


This seems to display the top 5 search terms for all lobs and all date ranges.

So any help would be greatly appreciated. I am looking at displaying the reports on a PHP page.

Thank you for your time.

Answer

If the number of distinct Lob is low .. you can use union

 (select Term, count(*) 
 from my_tbale 
 where Lob = 'JM Part A'
 group by Term, `Date`
 order by count(*) desc limit 5)
 union  
 (select Term, count(*) 
 from my_tbale 
 where Lob = 'JM Part B'
 group by Term, `Date`
 order by count(*) desc limit 5)