Search code examples
mysqlperformancepivot-table

MySQL - Faster Way To Create Columns Showing Count Values Based On Other Column

I have a table containing user data. For each user I have userID / company / license type amongst other columns. The table has about 15K rows and a structure something like:

UserName Company License
John CompA L1
Mary CompB L1
Andrew CompA L3
Archie CompC L2

...

I want to run a query which shows a count of the number of L1 and L2 license types against each company in different columns, something like this:

Company Count L1 Count L2
CompA 23 0
CompB 2 23
CompC 0 7
CompD 67 16

I can do this using the following SQL but the query runs for over 3 minutes:

select t.Company, 
  (select count(*) from tbl_users u where u.Company = t.Company and u.License = 'L1') as 'Count L1', 
  (select count(*) from tbl_users u where u.Company = t.Compamy and u.License = 'L2') as 'Count L2' 
  from tbl_users t group by Company;

I want this to publish results on an internal website that updates in real time, so that's not really going to work every time someone loads the page. I can very quickly (0.05 seconds) get a count of one license type with select Company, count(*) from tbl_users where License = 'L1' group by Company; and thought maybe something like this would work:

select tbl_users.company_member, tL1.L1, tL2.L2 from tbl_users,
left join 
    (select Company, count(*) as L1 from tbl_users where License = 'L1' group by Company as tL1) 
    on tbl_users.Company = tL1.Company
left join 
    (select Company , count(*) as L2 from tbl_users where License = 'L2' group by Company as tL2) 
    on tbl_users.Company = tL2.Company;

but I can't find a way to structure the query to make it work.

I know I could use a stored procedure to temporarily create the two tables then join them for the result but that seems overly complex for something I feel should be simple enough to put in a single query that doesn't run for over three minutes!

Solution

  • How long does this query take:

    select 
   t.Company,
   SUM(case when t.License='L1' then 1 end) as 'Count L1',
   SUM(case when t.License='L2' then 1 end) as 'Count L2'
from tbl_users t 
group by Company;

    It only need to check every record once, which should be quicker than your statement.