martin.softpro martin.softpro - 4 months ago 9
MySQL Question

mysql group by is multiplying my number of rows

i have a table with transfers (or trips, don't know what is the term) of patients. the transfer can be in ambulance, or in a car, it dependes on the "movil" column.
I have to totalize the number of transfers grouped by day and type of "movil".
Suppose this is mi table:

day movil
1 M75
1 M76
1 M3
2 M6
2 M80
3 M8
3 M4
3 M83
3 M4


movil M71,M72,M75,M76,M77,M81,M82,M83 are ambulances, all the rest are cars.
so i want to separate them between cars and ambulances.

heres' my query:

select
case when movil in ('M71','M72','M75','M76','M77','M81','M82','M83') then 'AMBULANCE'
else 'CAR' END as tipo,
DAY(fecha) as day,
COUNT(*) as total
from traslados
where YEAR(fecha) = '2016' and MONTH(fecha) = '07'
group by DAY(fecha),tipo
order by DAY(fecha)


so I should have 2 rows per day ,one corresponding to the ambulances , and another corresponding to the cars. however, I 'm getting 4 rows per day .
I can not understand why this happens

Answer

Try to use

SELECT CASE
       WHEN movil IN ('M71',
                      'M72',
                      'M75',
                      'M76',
                      'M77',
                      'M81',
                      'M82',
                      'M83') THEN 'AMBULANCE'
       ELSE 'CAR'
   END AS tipo,
   DAY(fecha) AS DAY,
   COUNT(*) AS total FROM traslados WHERE YEAR(fecha) = '2016'  AND MONTH(fecha) = '07' GROUP BY DAY(fecha),
     CASE
         WHEN movil IN ('M71',
                        'M72',
                        'M75',
                        'M76',
                        'M77',
                        'M81',
                        'M82',
                        'M83') THEN 'AMBULANCE'
         ELSE 'CAR'
     END
 ORDER BY DAY(fecha)