Calculate total of identical Ids

Asked

Viewed 80 times

1

I’m working with the postgresql , running the following query :

SELECT "tcfac"."co_cds_ficha_ativ_col",
       to_char(dt_ativ_col, 'DD/MM/YYYY') AS "data_atividade",
       to_char(hr_inicio, 'HH24:MI') AS "hora_inicio",
       to_char(hr_fim, 'HH24:MI') AS "hora_fim",
       "tcfac"."cod_equipe_ine",
       "tcfac"."qt_participante_ativ",
       "tcfac"."uni_codigo",
       "usr"."usr_nome",
       "uni"."uni_desc",
       "tctac"."no_cds_tipo_ativ_col"
FROM "tb_cds_ficha_ativ_col" AS "tcfac"
INNER JOIN "usuarios" AS "usr" ON tcfac.usr_codigo = usr.usr_codigo
INNER JOIN "unidade" AS "uni" ON tcfac.uni_codigo = uni.uni_codigo
INNER JOIN "tb_cds_tipo_ativ_col" AS "tctac" ON tcfac.tp_cds_ativ_col = tctac.co_cds_tipo_ativ_col
WHERE (tcfac.dt_ativ_col BETWEEN to_timestamp('05/08/2019', 'DD/MM/YYYY') AND to_timestamp('05/08/2019', 'DD/MM/YYYY'))
ORDER BY "tcfac"."uni_codigo" ASC

That returns me the following result:inserir a descrição da imagem aqui

My question is, would it be possible to add equal amounts of uni_code ? I will use that in a report. For example, two of these 4 queries are from the same unit. I believe bringing this directly from the database is better than dealing with it using PHP.

I made some changes in the Query after some comments but the result came out a bit confusing . inserir a descrição da imagem aqui

She didn’t count the Count’s just informed a:

SELECT "tcfac"."co_cds_ficha_ativ_col",
       to_char(dt_ativ_col, 'DD/MM/YYYY') AS "data_atividade",
       to_char(hr_inicio, 'HH24:MI') AS "hora_inicio",
       to_char(hr_fim, 'HH24:MI') AS "hora_fim",
       "tcfac"."cod_equipe_ine",
       "tcfac"."qt_participante_ativ",
       "tcfac"."uni_codigo",
       "usr"."usr_nome",
       "uni"."uni_desc",
       "tctac"."no_cds_tipo_ativ_col"
FROM "tb_cds_ficha_ativ_col" AS "tcfac"
INNER JOIN "usuarios" AS "usr" ON tcfac.usr_codigo = usr.usr_codigo
INNER JOIN "unidade" AS "uni" ON tcfac.uni_codigo = uni.uni_codigo
INNER JOIN "tb_cds_tipo_ativ_col" AS "tctac" ON tcfac.tp_cds_ativ_col = tctac.co_cds_tipo_ativ_col
WHERE (tcfac.dt_ativ_col BETWEEN to_timestamp('05/08/2019', 'DD/MM/YYYY') AND to_timestamp('05/08/2019', 'DD/MM/YYYY'))
ORDER BY "tcfac"."uni_codigo" ASC
php postgresql

  • You can use the GROUP BY, but why these 4 lines would be of the same unit being that two have uni_codigo equal to 505 and the other two equal to 506?

  • Use the COUNT aggregation function together with the GROUP BY clause. If you only want those who have duplicity then also use the HAVING clause.

  • Anderson Carlos Woss, so really are pairs had not attacked me to it. The group by does not work only when one Row is identical to another ?

  • Hello @Lucasalves, the answer I prepared solves your question?

1 answer

1


What you want is to return the total amount of unidades without impacting your current query by continuing to return the 4 records (taking into consideration its clause WHERE), right?

This is possible yes and I simulated an environment similar to yours with the DDL and DQL kerys (based on the information contained in its query), only to facilitate in my explanation (remember that I ignored the table tb_cds_tipo_ativ_col used in your query, since its absence would not impact on the construction of the answer).

I used in my solution the following subquery that does just what you mentioned, performing a count of all the units that are contained in the main query: 

(SELECT COUNT(*) FROM "tb_cds_ficha_ativ_col" AS "2tcfac"
WHERE "2tcfac"."uni_codigo" = "tcfac"."uni_codigo") as "qtd"

Turning your query to the next:

SELECT "tcfac"."co_cds_ficha_ativ_col",
       to_char(dt_ativ_col, 'DD/MM/YYYY') AS "data_atividade",
       to_char(hr_inicio, 'HH24:MI') AS "hora_inicio",
       to_char(hr_fim, 'HH24:MI') AS "hora_fim",
       "tcfac"."cod_equipe_ine",
       "tcfac"."qt_participante_ativ",
       "tcfac"."uni_codigo",
       "usr"."usr_nome",
       "uni"."uni_desc",
       "tctac"."no_cds_tipo_ativ_col",
        (SELECT COUNT(*) FROM "tb_cds_ficha_ativ_col" AS "2tcfac"
        WHERE "2tcfac"."uni_codigo" = "tcfac"."uni_codigo") as "qtd"
FROM "tb_cds_ficha_ativ_col" AS "tcfac"
INNER JOIN "usuarios" AS "usr" ON tcfac.usr_codigo = usr.usr_codigo
INNER JOIN "unidade" AS "uni" ON tcfac.uni_codigo = uni.uni_codigo
INNER JOIN "tb_cds_tipo_ativ_col" AS "tctac" ON tcfac.tp_cds_ativ_col = tctac.co_cds_tipo_ativ_col
WHERE (tcfac.dt_ativ_col BETWEEN to_timestamp('05/08/2019', 'DD/MM/YYYY') AND to_timestamp('05/08/2019', 'DD/MM/YYYY'))
ORDER BY "tcfac"."uni_codigo" ASC

Return of the consultation made on fiddle, previously quoted: inserir a descrição da imagem aqui

  • Just a quote from you: "...I believe that bringing this directly from the bank is better than dealing with using PHP...." This depends a lot on what your concept of best and other factors such as complexity of the query, performance, maintainability, etc. Within the application you are working on. I hope to have helped.

  • 1

    Thank you also for addressing the heat of my doubt as to which is better to use the bank or the language . Helped and much.

  • For nothing, I’m happy to help

Browser other questions tagged php postgresql

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