As I understand the question, you don't need a window function. Aggregate functions do the job:
count()
in the lowest level (-> row_ct
).
sum()
the resulting row_ct
in the next level (-> total_row_ct
).
SELECT row_to_json(selected_records)::text AS data
FROM (
SELECT array_to_json(array_agg(row_to_json(records))) AS data
, sum(row_ct) AS total_row_ct
FROM (
SELECT landing_path_id
, sum(entrances) AS entrances
, count(*) AS row_ct
FROM report_la
WHERE profile_id = 3777614
GROUP BY landing_path_id
LIMIT 10
) records
) selected_records;
I also included landing_path_id
so the resulting data makes sense.
SQL Fiddle.
Window function?
A window function (count(*) over ()
) does not seem to be what you want, since you don't have unaggregated rows.
You could add to the inner subquery:
count(*) OVER ()
.. to get the count of distinct landing_path_id
, which is one other possible number that might be of interest. But that doesn't seem to be what you meant by "the total number of rows from that records select".
Or you could add to the inner subquery:
sum(count(*)) OVER ()
.. to get the total count with every landing_path_id
redundantly, but that would seem pointless. Just mentioning that to demonstrate it's possible to run a window function over the result of an aggregate function in a single pass. Details for that:
Updated question
Your result, just without total_count
in the records
subquery. Now accounting for the LIMIT
in the inner SELECT
. Even though a maximum of 10 distinct landing_path_id
is selected, all qualifying landing_path_id
are counted.
To get both in one scan and reuse count and sum separately I introduce a CTE:
WITH cte AS (
SELECT sum(entrances) AS entrances
, count(*) over () AS total_count
FROM report_la
WHERE profile_id = 3777614
GROUP BY landing_path_id
LIMIT 10
)
SELECT row_to_json(selected_records)::text AS data
FROM (
SELECT (SELECT total_count FROM cte LIMIT 1) AS total_count
, array_to_json(array_agg(row_to_json(records))) AS data
FROM (SELECT entrances FROM cte) records
) selected_records;
If you don't care about the attribute name, you can have that cheaper with a subquery:
SELECT row_to_json(selected_records)::text AS data
FROM (
SELECT min(total_count) AS total_count
, array_to_json(array_agg(row_to_json(ROW(entrances)))) AS data
FROM (
SELECT sum(entrances) AS entrances
, count(*) over () AS total_count -- shouldn't show up in result
FROM report_la
WHERE profile_id = 3777614
GROUP BY landing_path_id
LIMIT 1
) records
) selected_records;
You get the default attribute name f1
instead of entrances
, since the ROW
expression does not preserve the column name.
If you need a certain attribute name, you could cast the row to a registered type. (Ab-)using a TEMP TABLE
to register my row type for the session:
CREATE TEMP TABLE rec1 (entrances bigint);
...
, array_to_json(array_agg(row_to_json(ROW(entrances)::rec1))) AS data
...
This would be a bit faster than the CTE. Or, more verbose but without cast:
...
, array_to_json(array_agg(row_to_json(
(SELECT x FROM (SELECT records.entrances) x)))) AS data
...
Detailed explanation in this related answer:
SQL Fiddle.
Assumptions and Clarification
- Postgres 9.4
- You want various counts from the same given table.
desc
is a reserved word in SQL, don't use it as identifier. Using descr
instead.
1. Basic query
The fastest and most elegant way to get multiple partial counts from the same table is a single SELECT
statement with multiple aggregate functions using the FILTER
clause:
A statement like:
SELECT count(*) FILTER (WHERE size > width) AS ct1
, count(*) FILTER (WHERE size > height) AS ct2
, count(*) FILTER (WHERE width <> height) AS ct3
FROM testtable;
Or, for Postgres 9.3:
SELECT count(size > width OR NULL) AS ct1
, ...
FROM testtable;
2. Advanced query
To get the result in the form you want it, I counter-pivot with a VALUES
expression in a LATERAL
join, adding the ID and description id, descr
in the process:
SELECT x.*
FROM (
SELECT count(*) FILTER (WHERE size > width) AS ct1
, count(*) FILTER (WHERE size > height) AS ct2
, count(*) FILTER (WHERE width <> height) AS ct3
FROM testtable
) t
, LATERAL (
VALUES (1, 'size should not greater than width' , ct1)
, (2, 'size should not greater than height', ct2)
, (3, 'with should be equal to height' , ct3)
) x(id, descr, ct);
Bold parts come from the errortable
when building the statement dynamically:
3. Dynamic query
To concatenate the above statement dynamically from values provided in errortable
:
CREATE OR REPLACE FUNCTION
SELECT 'SELECT x.* FROM (SELECT '
|| string_agg(
format('count(*) FILTER (WHERE %I %s %I) AS c%s'
, field1, operator, field2, id)
, ', ')
|| ' FROM testtable) t, LATERAL (VALUES ('
|| string_agg(format('%s, %L, c%s', id, descr, id), '), (')
|| ')) x(id, dscr, ct)'
FROM errortable;
format()
with %I
quotes column names where necessary and makes the statement safe against SQL injection - except for operator
, which is concatenated as is. You might be able to make that safe, too, with the OPERATOR()
construct ...
If untrusted users don't have write access to errortable
, you can control its content and need not worry.
4. Full automation
Since the return type is uniform and well known, we can encapsulate it in a function:
CREATE OR REPLACE FUNCTION f_error_count()
RETURNS TABLE (id int, descr text, ct bigint) AS
$func$
BEGIN
RETURN QUERY EXECUTE (
SELECT format('SELECT x.* FROM (SELECT %s FROM testtable) t
, LATERAL (VALUES (%s)) x(id, dscr, ct)'
, string_agg(format('count(*) FILTER (WHERE %I %s %I) AS c%s'
, e.field1, e.operator, e.field2, e.id), ', ')
, string_agg(format('%s, %L, c%s', e.id, e.descr, e.id), '), ('))
FROM errortable e
);
END
$func$ LANGUAGE plpgsql;
Call:
SELECT * FROM f_error_count();
Simplifying the concatenation by one more step with an outer format()
. Also note how all columns are table-qualified to avoid conflicts.
Again, since dynamic SQL is involved, make sure it cannot be abused for SQL injection.
SQL Fiddle for Postgres 9.3 since 9.4 is not available.
Best Answer
you can use conditional aggregation for that: