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.
You can't use an aggregate function in a condition, you need to use a sub-select
SELECT EntryID
FROM AttendanceREcords
WHERE StaffID = 'xxxxx'
AND ArrivalTime = (select min(ArrivalTime)
from AttendanceREcords
where StaffID = 'xxxxx');
But this can be done more efficiently by using a LIMIT
clause:
SELECT EntryID
FROM AttendanceREcords
WHERE StaffID = 'xxxxx'
ORDER BY arrivalTime
LIMIT 1
There is a difference between the two statements: if more than one row has the same minimum arrival time, the first one will return all of them, the second one only one row.
Another alternative that is usually more efficient that a sub-select is using a window function:
SELECT EntryID
FROM (
SELECT EntryID,
dense_rank() over (order by arrivalTime) as rnk
FROM AttendanceREcords
WHERE StaffID = 'xxxxx'
) t
where rnk = 1;
By changing the order by arrivalTime
you can select the first or the last.
If you want the first and last in a single query you can do something like this:
SELECT EntryID,
arrivalTime
FROM (
SELECT EntryID,
min(arrivalTime) as min_time,
min(arrivalTime) as max_time,
FROM AttendanceREcords
WHERE StaffID = 'xxxxx'
) t
where arrival_time = min_time
or arrival_time = max_time;
Best Answer
You need to cast
created_at
as adate
- that will remove the time.now()
also contains a time, so you need to usecurrent_date
And finally your
IN (..)
is not needed, you can apply that condition directly in thewhere
clause:Note that the above will not use an index on
created_at
. If the performance is not acceptable and the condition would benefit from using an index, you can use something like this: