CODEWITHSUNDEEP
mysqlsql
Albert
Albert

Reputation: 1526

How can I combine these 3 different queries into one?

I have 3 queries in a MySQL database that output all the same fields except the last one, which is a calculation. I would like, if possible, to combine them into one query that outputs the common fields plus the additional calculated fields.

These are the queries:

Query 1:

SELECT 
    accesslogs.login, 
    usuarios.login, 
    usuarios.nombre, 
    usuarios.apellido, 
    COUNT(*)
FROM 
    accesslogs 
    LEFT JOIN usuarios ON accesslogs.login = usuarios.id_usuario
WHERE
    accesslogs.id_campania=173 
    AND accesslogs.tipo_acceso='A' 
    AND usuarios.id_usuario != 6658
GROUP BY accesslogs.login 
ORDER BY usuarios.login ASC

Query 2: 

SELECT 
    accesslogs.login, 
    usuarios.login, 
    usuarios.nombre, 
    usuarios.apellido, 
    MIN(accesslogs.fecha_registro) 
FROM 
    accesslogs 
    LEFT JOIN usuarios ON accesslogs.login = usuarios.id_usuario
WHERE 
    accesslogs.id_campania=173 
    AND accesslogs.tipo_acceso='A' 
    AND usuarios.id_usuario != 6658
GROUP BY accesslogs.login 
ORDER BY usuarios.login ASC

Query 3:

SELECT 
    accesslogs.login, 
    usuarios.login, 
    usuarios.nombre, 
    usuarios.apellido, 
    COUNT(*) 
FROM 
    accesslogs 
    LEFT JOIN usuarios ON accesslogs.login = usuarios.id_usuario
WHERE 
    accesslogs.id_campania=173 
    AND accesslogs.tipo_acceso='U' 
    AND usuarios.id_usuario != 6658
GROUP BY accesslogs.login 
ORDER BY usuarios.login ASC

You can notice that the first 4 fields in the SELECT are always the same. The output I'm looking for is something like:

Field 1 | Field 2 | Field 3 | Field 4 | CF Query 1 | CF Query 2 | CF Query 3

CF: calculated field, last field in the SELECT of the corresponding query

Upvotes: 1

Views: 71

Answers (2)

GMB
GMB

Reputation: 222402

The following query should get the job done. I changed the WHERE condition on tipo_acceso to a IN clause and used conditional aggreation to compute the counts and the min.

SELECT 
    a.login, 
    u.login, 
    u.nombre, 
    u.apellido, 
    SUM(a.tipo_acceso = 'A') cnt_A,
    MIN(CASE WHEN a.tipo_acceso = 'A' THEN a.fecha_registro END) min_fecha_registro,
    SUM(a.tipo_acceso = 'U') cnt_U
FROM 
    accesslogs a
    LEFT JOIN usuarios u ON a.login = u.id_usuario
WHERE 
    a.id_campania = 173 
    AND a.tipo_acceso IN ('A', 'U')
    AND u.id_usuario != 6658
GROUP BY 
    a.login, 
    u.login, 
    u.nombre, 
    u.apellido 
ORDER BY 
    u.login ASC

Other notable points:

  • all non-aggregated columns must appear in the GROUP BY clause; on any RDBMS other than old versions of MySQL, failing to comply with this rule generates a fatal error

  • I used table aliases to shorten the query

Upvotes: 1

Vishnudev Krishnadas
Vishnudev Krishnadas

Reputation: 10960

You could use UNION

SELECT accesslogs.login, usuarios.login, usuarios.nombre, 
    usuarios.apellido, COUNT(*)
FROM accesslogs 
  LEFT JOIN usuarios ON accesslogs.login = usuarios.id_usuario
where accesslogs.id_campania=173 
and accesslogs.tipo_acceso='A' 
and usuarios.id_usuario != 6658
GROUP BY accesslogs.login 
ORDER BY usuarios.login ASC
UNION
SELECT accesslogs.login, usuarios.login, usuarios.nombre, 
    usuarios.apellido, min(accesslogs.fecha_registro) 
FROM accesslogs 
  LEFT JOIN usuarios
  ON accesslogs.login = usuarios.id_usuario
where accesslogs.id_campania=173 
and accesslogs.tipo_acceso='A' 
and usuarios.id_usuario != 6658
GROUP BY accesslogs.login 
ORDER BY usuarios.login ASC
UNION
SELECT accesslogs.login, usuarios.login, usuarios.nombre, 
    usuarios.apellido, COUNT(*) 
FROM accesslogs 
  LEFT JOIN usuarios ON accesslogs.login = usuarios.id_usuario
where accesslogs.id_campania=173 
and accesslogs.tipo_acceso='U' 
and usuarios.id_usuario != 6658
GROUP BY accesslogs.login 
ORDER BY usuarios.login ASC

Upvotes: 0

Related Questions