problem with 2-table MySQL query

Question

problem with 2-table MySQL query

Navigation

#1 by (1 votes)

0

I have a problem with the following query

SELECT h.sexo,
       h.universidad, 
       count(*) as TOTALES, 
       SUM(CASE WHEN h.universidad = 'Latina' THEN 1 ELSE 0 END) AS number,
       SUM(CASE WHEN h.universidad = 'UAM' THEN 1 ELSE 0 END) AS numbers
from citas as u 
join pacientes as h
  on u.cedulaFK = h.cedula 
  AND u.fecha_cita >= '$fecha1' and u.fecha_cita <= '$fecha2'
where u.cod_sedeFK = 1
ORDER BY h.sexo

I want to show the number of citations by sex or gender, in addition to the university to which they belong. But he's just showing me the number of one-sex citations, omitting the others.

mysql join

asked by Oscar Alberto Rodriguez 03.12.2018 в 20:04

source

1 answer

Replace string in txt or flat file with PHP How to print all the divisible numbers of a number entered by keyboard? Java For

score 1 · Accepted Answer

With a GROUP BY in sex.

SELECT 
    p.sexo,
    p.universidad, 
    COUNT(*) as TOTALES, 
    SUM(CASE 
            WHEN p.universidad = 'Latina' 
            THEN 1 
            ELSE 0 
        END) AS number,
    SUM(CASE 
            WHEN p.universidad = 'UAM' 
            THEN 1 
            ELSE 0 
        END) AS numbers
FROM citas as c 
    JOIN pacientes as p
        ON c.cedulaFK = p.cedula 
        AND c.fecha_cita >= '$fecha1' -- Esto no podría ir dentro del WHERE?
        AND c.fecha_cita <= '$fecha2' -- Junto con esto?
WHERE 
    c.cod_sedeFK = 1
GROUP BY 
    p.sexo, p.universidad -- agrupas por sexo y universidad