CODEWITHSUNDEEP
sqlpostgresqlsubquery
neu242
neu242

Reputation: 16575

SQL subquery using group by item from main query

I have a table with a created timestamp and id identifier.

I can get number of unique id's per week with:

SELECT date_trunc('week', created)::date AS week, count(distinct id) 
FROM my_table
GROUP BY week ORDER BY week;

Now I want to have the accumulated number of created by unique id's per week, something like this:

SELECT date_trunc('week', created)::date AS week, count(distinct id),
       (SELECT count(distinct id)
        FROM my_table
        WHERE date_trunc('week', created)::date <= week) as acc
FROM my_table
GROUP BY week ORDER BY week;

But that doesn't work, as week is not accessible in the sub select (ERROR: column "week" does not exist).

How do I solve this?

I'm using PostgreSQL

Upvotes: 1

Views: 763

Answers (2)

MhQ-6
MhQ-6

Reputation: 328

CTEs or a temp table should fix your problem. Here is an example using CTEs. 

 WITH abc AS (
     SELECT date_trunc('week', created)::date AS week, count(distinct id) as IDCount
     FROM my_table
     GROUP BY week ORDER BY week;
 )

SELECT abc.week, abc.IDcount,
       (SELECT count(*)
        FROM my_table
        WHERE date_trunc('week', created)::date <= adc.week) as acc
FROM abc
GROUP BY week ORDER BY abc.week;

Hope this helps

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Use a cumulative aggregation. But, I don't think you need the distinct, so:

SELECT date_trunc('week', created)::date AS week, count(*) as cnt,
       SUM(COUNT(*)) OVER (ORDER BY MIN(created)) as running_cnt
FROM my_table
GROUP BY week
ORDER BY week;

In any case, as you've phrased the problem, you can change cnt to use count(distinct). Your subquery is not using distinct at all.

Upvotes: 2

Related Questions