CODEWITHSUNDEEP
mysqlsqlperformance
AZ-T
AZ-T

Reputation: 23

MYSQL: Two counts, one group... better way?

I have a table that looks like this...

*------------------------------------*
|  id  |  state  |  name  |  status  |
|------|---------|--------|----------|
|  1   |   AZ    |  Tim   |   OK     |
|  2   |   AZ    |  Jon   |   BAD    |
|  3   |   NV    |  Bob   |   BAD    |
|  4   |   NV    |  Ted   |   OK     |
|  5   |   NV    |  Don   |   BAD    |
|  6   |   CO    |  Guy   |   OK     |
|  7   |   CO    |  Jed   |   OK     |
|  8   |   CA    |  Cal   |   BAD    |
|  9   |   CA    |  Tom   |   OK     |
|  10  |   CA    |  Ian   |   OK     |
*------------------------------------*

I need to produce a result that looks like this...

*--------------------------------*
|  state  | total  |  totalgood  |
|---------|--------|-------------|
|   AZ    |  2     |   1         |
|   NV    |  3     |   1         |
|   CO    |  2     |   2         |
|   CA    |  3     |   2         |
*--------------------------------*

I can do it like this...

SELECT state AS state, 
  COUNT(*) AS total, 
  SUM(CASE WHEN status = 'OK' THEN 1 ELSE 0 END) AS totalgood
FROM mytable 
WHERE state IN ('AZ','NV','CO','CA') 
GROUP BY state;

My concern is that this is a VERY large table w/ over 100k records. My understanding is that using SUM causes it to loop through each row while it counts. That could be a pretty big performance hit.

So is there a better way to do this? Or... is my concern unfounded? I recognize that I'm not as well-versed in MySQL as I could be, and I have a hard time getting my mind around JOINs. So any advise is appreciated.

(* Note that the WHERE clause, while not necessary here, will be for my real application.)

Upvotes: 2

Views: 76

Answers (2)

Arma77
Arma77

Reputation: 23

Try using WITH ROLLUP-Function in Group By

DOC

Upvotes: 0

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

It looks good to me. If you are concerned about using SUM you can easily convert it to COUNT:

COUNT(CASE WHEN status = 'OK' THEN 1 ELSE NULL END) AS totalgood

BTW: 100k rows isn't considered large anymore.

Upvotes: 2

Related Questions