There is such a request:

SELECT r.id_nomenclature, n.name AS "name_nomenclature", COUNT(n.name) OVER (PARTITION BY rg.id_rsd_group) AS "count_nomenclature", rg.id_rsd_group AS "id_group", r.id AS "id_expend" FROM rsd AS r LEFT JOIN rsd_group AS rg ON rg.id_rsd = r.id LEFT JOIN nomenclature AS n ON n.id = r.id_nomenclature GROUP BY r.id_nomenclature, n.name, n.id, rg.id_rsd_group, r.id ORDER BY rg.id_rsd_group DESC;

Here is his conclusion:

enter image description here

I need to write in count_nomenclature the number of identical name_nomenclature being in the same id_group group. but this query counts the number of all name_nomenclature in the id_group group, despite the fact that COUNT (n.name) is written there.

it turns out that the written COUNT (n.name) is equivalent to COUNT (*), so it should not be.

DBMS postgree. How to fix?

  • And over here then why? is simply COUNT (n.name) not enough? - Akina
  • @Akina well, there will be some units. - perfect
  • What do you want? it is not clear ... get with one grouping ( GROUP BY r.id_nomenclature, n.name, n.id, rg.id_rsd_group, r.id ) data for another group ( GROUP BY n.name )? By the way, why does the grouping also by n.id? - Akina
  • @Mike gave the correct answer - perfect
  • one
    @perfect Yes, I already see. Next time try to formulate the question more accurately. Best of all, in addition to the resulting data, to give another sample of what should happen, it is much faster and more accurate to guess what is needed :) - Mike

1 answer 1

COUNT(n.name) counts the number of lines by which n.name not NULL.

Do you need the number of lines with the same name and id_group ? If so, try this:

 COUNT(1) OVER (PARTITION BY rg.id_rsd_group, n.name) AS "count_nomenclature",
  • one
    yes this is what you need, thank you very much. - perfect