question about group by

Question

Hi

in mysql how to write a sql like this, to get the amount of X > 20 and <20

select date, numberOfXMoreThan20,numberOfXLessThan20, otherValues

from table

group by (date, X>20 and X<20)

my way, but i think it's not good

select less20.id_date, a,b
from (select id_date,count(Duree_Attente_Avant_Abandon) as a from cnav_reporting.contact_global where Duree_Attente_Avant_Abandon>20 group by id_date) as less20, (select id_date,count(Duree_Attente_Avant_Abandon) as b from cnav_reporting.contact_global where Duree_Attente_Avant_Abandon<20 group by id_date) as more20

where

less20.id_date=more20.id_date

thanks

Answer 1

You're probably looking for the COUNT aggregate:

SELECT COUNT(*) FROM table Where X > 20

Answer 2

SELECT
  date,
  SUM( IF(X > 20), 1, 0 ) AS overTwenty,
  SUM( IF(X < 20), 1, 0 ) AS belowTwenty,
  otherValue
FROM `table`
GROUP BY `date`, `otherValue`