Add multiple fields from the same table in the same query

Asked

Viewed 221 times

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 registrations in April with type 1
- Number of registrations in April with type 2
- Number of registrations in March
- Number of registrations in March with type 1
- Number of registrations in March with type 2

All I need are April and May records.

I tried using COUNT with IF (as in Mysql) but it didn’t work.

query informix

  • Uses the CASE , would be a solution similar to that suggested by @Cesarmiguel , but without using subselect... To learn how to use CASE , search IBM Knowledge Center. (on this IBM page, change the language to English, before researching, it does not work right)

  • @Filipemoraes the answer solved your question?

  • @Cesarmiguel yes, but I used CASE in place of several selects, as ceinmart reported in the comment above. Can change the response using CASE to mark it as right?

  • @Filipemoraes, I can, but now I have little time. Then I warn you when to edit

  • @Filipemoraes, I already added. See what you think

1 answer

0


You can do several SELECT for the intended 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 the CASE, and similarly to the various select of the above solution, you will make a 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 on THEN is irrelevant.

Browser other questions tagged query informix

You are not signed in. Login or sign up in order to post.