user113531 user113531 - 4 months ago 5
SQL Question

SQL how to use values in a query for another query

I have a list of dates which I can generate using:

SELECT date from
generate_series(
'2016-05-09'::date,
CURRENT_DATE,
'1 day'::interval
) date


I want to perform another query on a table using each value in the above list:
An example of what I want to achieve for one of the date value:

SELECT COUNT(*) FROM table,
WHERE table.datecolumn > date


How do I perform the second query for all the values in the first query to get final output somewhat in the form:

datecol count
2016-07-09 100
2016-07-10 200
2016-07-11 100

Answer

I'd use LATERAL join. See 7.2.1.5. LATERAL Subqueries in Postgres docs.

SELECT
    dates.dt, Counts.c
FROM
    generate_series(
        '2016-05-09'::date,
        CURRENT_DATE,
        '1 day'::interval
    ) AS dates(dt)
    INNER JOIN LATERAL
    (
        SELECT COUNT(*) AS c
        FROM table
        WHERE table.datecolumn > dates.dt
    ) AS Counts ON true
Comments