CODEWITHSUNDEEP
mysql
chun
chun

Reputation: 847

question about group by

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

Upvotes: 1

Views: 60

Answers (2)

Seb
Seb

Reputation: 25157

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`

Upvotes: 4

SLaks
SLaks

Reputation: 888283

You're probably looking for the COUNT aggregate:

SELECT COUNT(*) FROM table Where X > 20

Upvotes: 1

Related Questions