CODEWITHSUNDEEP
mysqlsqldatabase
newbieprogrammer
newbieprogrammer

Reputation: 878

mySQL count table efficiently

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;

Upvotes: 2

Views: 64

Answers (3)

jarlh
jarlh

Reputation: 44795

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), ...

Upvotes: 0

Ionic
Ionic

Reputation: 3935

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

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

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

Upvotes: 2

Related Questions