mySQL count table efficiently

Question

myTable

Basically I have a table name myTable and inside has Name,Class , I would like to sort them out like the table below with a count of each name and number of 1,2,3s they have.Below is the codes I currently had but it work on small amount of entries, if the table has 20,000 lines it took a very long time to execute.

Select DISTINT(Name),
       (Select count(*) from `myTable` d1 where d1.Name = dd.Name and Class=1),
       (Select count(*) from `myTable` d2 where d2.Name = dd.Name and Class=2),
       (Select count(*) from `myTable` d3 where d3.Name = dd.Name and Class=3)
from `myTable` dd
Order by Name;

Giorgi Nakeuri · Accepted Answer · 2015-06-24 08:00:21Z

2

It is classic conditional aggregation:

Select Name,
       sum(case when Class = 1 then 1 else 0 end),
       sum(case when Class = 2 then 1 else 0 end),
       sum(case when Class = 3 then 1 else 0 end)
from myTable
group by Name

answered Jun 24, 2015 at 8:00

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

newbieprogrammer Over a year ago

thanks for your efficient answer, the query time for 50,000 entries is so much faster now.

Ionic · Accepted Answer · 2015-06-24 08:01:40Z

0

You can pivot it:

SELECT t.Name,
    SUM(if(t.Class=1,1,null)) as Class1,
    SUM(if(t.Class=2,1,null)) as Class2,
    SUM(if(t.Class=3,1,null)) as Class3
FROM myTable t
GROUP BY t.Name

answered Jun 24, 2015 at 8:01

Ionic

3,9451 gold badge14 silver badges33 bronze badges

Comments

jarlh · Accepted Answer · 2015-06-24 08:05:32Z

0

Do a GROUP BY with conditional counting:

select Name,
       count(case when class = 1 then 1 end),
       count(case when class = 2 then 1 end),
       count(case when class = 3 then 1 end)
from `myTable`
group by Name;

Note: DISTINCT is not a function on a column, it works on the whole selected rows. (It doesn't matter if the column-name is put within parentheses or not.)

select distinct(col1), col2, ...

is the same as

select distinct col1, col2, ...

and also the same as

select distinct col1, (col2), ...

edited Jun 24, 2015 at 8:05

answered Jun 24, 2015 at 8:00

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Collectives™ on Stack Overflow

mySQL count table efficiently

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related