Return total number of rows and selected (aggregated) data

I have a function which is selecting some data from a table. I would like to return the selected data and the total number of rows in that table.

How can I do that or how can I get the same result in the most efficient way?

I have tried few things and I ended up with the code below, now this is the format that I want, but count(*) over () as total_count will return 1 all the time, what I need it to return is the total number of rows from that records select.

SELECT 
row_to_json(selected_records) as data
FROM
(   
    SELECT
    count(*) over () as total_count,
    array_to_json(array_agg(row_to_json(records))) as data
    FROM (
        SELECT
            sum(entrances) as entrances
        FROM report_la
        WHERE profile_id = 3777614
        GROUP BY landing_path_id
        limit 10 offset 0
    ) records
) as selected_records

Updated, the code below is producing the result that I want, it will be nice if I can hide that total_count column from the records select

SELECT 
row_to_json(selected_records) as data
FROM
(   
    SELECT
    min(total_count) as total_count
    ,array_to_json(array_agg(row_to_json(records))) as data
    FROM (
        SELECT
            sum(entrances) as entrances
            ,count(*) over () as total_count
        FROM ga.report_la
        WHERE ga_profile_id = 3777614
        GROUP BY landing_path_id
        limit 10
    ) records
) as selected_records

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:

• How will the order of operations in this query affect my results?

• How do I get the aggregate of a window function in Postgres?

• Need separate columns that calculate percentage

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:

• Select columns inside json_agg

SQL Fiddle.

StackExchange Database Administrators Q#84322, answer score: 6