# Return total number of rows and selected (aggregated) data

## All we need is an easy explanation of the problem, so here it is.

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
``````

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 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:

#### 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.

### Method 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;
``````

### Method 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
``````

### Method 4

Put your aggregated query into subquery

``````SELECT COUNT( * )
FROM (
SELECT COUNT( * ) AS  `count`
FROM lms