armate1hernan armate1hernan - 3 months ago 7
MySQL Question

How I can count with GROUP BY and condition in MySQL

I have this table:

Signups group by _legajo

And I need:

Indexes

Where:


  • Complete have all records with same _legajo and _count > 0

  • Zero have all records with same_legajo and _count = 0

  • Track have all records with same _legajo, some records with _count 0 and some with _count >



My table is 20k records

I working on PHP and MySQL

some ideas?

Answer

You could first select MIN and MAX for each _legajo:

SELECT _legajo, MIN(_count), MAX(_count)
  FROM signups
  GROUP BY _legajo;

If MAX is zero, then it naturally belongs to the zero category. If MIN is larger than zero then it belongs to complete category (MAX is naturally larger than zero then). If MIN is zero and MAX isn't, then it's in track.

Then you can just use SUM to determine the counts with the conditions:

SELECT
  SUM(CASE WHEN min>0 THEN 1 ELSE 0 END) as complete,
  SUM(CASE WHEN max=0 THEN 1 ELSE 0 END) as zero,
  SUM(CASE WHEN min=0 AND max>0 THEN 1 ELSE 0 END) as track
FROM (SELECT _legajo, MIN(_count), MAX(_count)
  FROM signups
  GROUP BY _legajo) a;
Comments