Add multiple fields from the same table in the same query

Question

Add multiple fields from the same table in the same query

Navigation

#1 by (0 votes)

2

I have an "X" table with several fields and one of the fields is "date" and the other is "type".

In the same query I need to return the following:
- Number of registrations in April
- Number of records in April with type 1
- Number of records in April with type 2
- Number of registrations in March
- Number of records in March with type 1
- Number of records in March with type 2

I only need records for April and May.

I tried to use COUNT with the IF (as in MySql) but it did not work.

query informix

asked by anonymous 20.05.2015 / 17:36

1 answer

How to configure Apache via command line? Error messageContext.add

score 0 · Accepted Answer

You can make multiple SELECT for the desired result:

SELECT 
    (SELECT COUNT (*) FROM tbl_x WHERE MONTH(dataCriacao) = 4) AS Numero_registos_Abril,
    (SELECT COUNT (*) FROM tbl_x WHERE MONTH(dataCriacao) = 4 AND tipo = 1) AS Numero_registos_Abril_tipo1,
    (SELECT COUNT (*) FROM tbl_x WHERE MONTH(dataCriacao) = 4 AND tipo = 2) AS Numero_registos_Abril_tipo2,
    (SELECT COUNT (*) FROM tbl_x WHERE MONTH(dataCriacao) = 3) AS Numero_registos_Marco,
    (SELECT COUNT (*) FROM tbl_x WHERE MONTH(dataCriacao) = 3 AND tipo = 1) AS Numero_registos_Marco_tipo1,
    (SELECT COUNT (*) FROM tbl_x WHERE MONTH(dataCriacao) = 3 AND tipo = 2) AS Numero_registos_Marco_tipo2,
FROM tbl_x

EDIT:

Using CASE , and similarly to the various select of the solution above, you will make COUNT for each of your conditions:

SELECT 
    COUNT(CASE WHEN MONTH(dataCriacao) = 4 THEN 1 END) AS Numero_registos_Abril,
    COUNT(CASE WHEN MONTH(dataCriacao) = 4 AND tipo = 1 THEN 1 END) AS Numero_registos_Abril_tipo1,
    COUNT(CASE WHEN MONTH(dataCriacao) = 4 AND tipo = 2 THEN 1 END) AS Numero_registos_Abril_tipo2,
    COUNT(CASE WHEN MONTH(dataCriacao) = 3 THEN 1 END) AS Numero_registos_Marco,
    COUNT(CASE WHEN MONTH(dataCriacao) = 3 AND tipo = 1 THEN 1 END) AS Numero_registos_Marco_tipo1,
    COUNT(CASE WHEN MONTH(dataCriacao) = 3 AND tipo = 2 THEN 1 END) AS Numero_registos_Marco_tipo2,
FROM tbl_x

In this example, the value you put in THEN is irrelevant.