CODEWITHSUNDEEP
sqlpostgresqlselect
hangc
hangc

Reputation: 5473

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

Upvotes: 1

Views: 754

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

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

Upvotes: 1

Related Questions