Return total number of rows and selected (aggregated) data

Question

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

Answer 1

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.

Answer 2

You can do what a_horse_with_no_name or ForguesR suggested (I believe a_horse_with_no_name's suggestion is more efficient than ForguesR).. However, those will give you an additional column in your result set - which you may or may not want. It sort of depends on if you want that additional column (all showing the same data repeated) or if you want a row in your result set that's akin to a "summary row"..

My answer will address if you are interested in a "summary row" type of result.

Another option would be to have a query that gives you the COUNT(*) that you are looking for and then UNION it with your original query. The trick to this, of course, is to make sure that you have the same number of columns in both queries and that the COUNT(*) query ends up last. To do that, I have added a column that's used to sort by.. See simple example below.

-- assume col1, col2 are VARCHAR and col3, col4 are NUMERIC
SELECT
  col1, col2, col3, col4
FROM (
  SELECT
    col1, col2, col3, col4, 1 AS sorter
  FROM tab1
  UNION
  SELECT
    NULL::VARCHAR AS col1, NULL::VARCHAR AS col2, NULL::NUMERIC AS col3, COUNT(*) AS col4, 2 AS sorter
  FROM tab1
) a
ORDER BY a.sorter, a.col1, a.col2;

Answer 3

Just include a subquery to get the count of all the rows in your table. Something like :

SELECT *, (SELECT COUNT(*) FROM yourTable) AS TotalNbRows FROM yourTable

Answer 4

Put your aggregated query into subquery

SELECT COUNT( * ) 
  FROM (
  SELECT COUNT( * ) AS  `count` 
  FROM lms
  WHERE `lead_school_type` =3
  AND subscription.vad =1
  GROUP BY lms.user_id
) AS subcount