How to make COUNT work with GROUP BY?

Question

PostgreSQL

In general, the situation is this. It is necessary to count the number of lines in the response of a query like:

SELECT o.id AS cnt
FROM "order" AS o
         LEFT JOIN product p ON (p.order_id = o.id)
GROUP BY o.id
HAVING sum(p.price) >= 10

As usual, I try to do this through COUNT. Yes, it was not there, several records are returned, such as [1, 1, ....]. I understand the problem in grouping - the aggregate function is applied to each group of records. And I can’t figure out how to get around this and get a normal result in the form of INT.
Phpsh functions like count () do not offer. Same on any it is possible to make on pure SQL.

Answer 1

It is necessary to count the number of lines in the response of a query like

There are several options.
The first one, the most correct one, is to get this value in a separate request.

SELECT COUNT(*)  AS cnt
FROM (
    SELECT 1
    FROM "order" AS o
         LEFT JOIN product p ON (p.order_id = o.id)
    GROUP BY o.id
    HAVING sum(p.price) >= 10
) x

The second is to use the programming language tools or access libraries to get the number of records in the recordset. Usually a recordset has a similar property.
The third is to add resp. field to each entry in the output set using a window function, and take the value from any entry:

SELECT o.id, COUNT(o.id) OVER () AS cnt
FROM "order" AS o
         LEFT JOIN product p ON (p.order_id = o.id)
GROUP BY o.id
HAVING sum(p.price) >= 10