CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Kevin
Kevin

Reputation: 91

Postgres select records first seen yesterday

I'm trying to select all records that were first seen in the last day along with some other aggregates but I can't figure it out as aggregates aren't allowed in the where clause.

This was my attempt:

SELECT  m.m_id, 
        m, 
        SUM(count) AS e_count, 
        COUNT(DISTINCT(c_code)) AS c_count 
FROM e 
  LEFT JOIN m ON e.m_id = m.m_id 
WHERE   MIN(e_t::date) = current_date - INTERVAL '1' day 
GROUP BY 1 
ORDER BY 3 DESC;

I also had a play with an sub query but didn't get it to work. Any ideas how to do this?

Upvotes: 0

Views: 317

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

You just need to put the logic in a HAVING clause:

SELECT m.m_id, m, SUM(count) AS e_count, 
       COUNT(DISTINCT(c_code)) AS c_count 
FROM e LEFT JOIN
     m
     ON e.m_id = m.m_id 
GROUP BY 1 
HAVING MIN(e_t::date) = current_date - INTERVAL '1' day 
ORDER BY 3 DESC;

However, you might find that filtering before the aggregation is more efficient. You can do that with a WHERE clause and NOT EXISTS:

SELECT m.m_id, m, SUM(count) AS e_count, 
       COUNT(DISTINCT(c_code)) AS c_count 
FROM e LEFT JOIN
     m
     ON e.m_id = m.m_id 
WHERE NOT EXISTS (SELECT 1
                  FROM e e2
                  WHERE e2.m_id = e.m_id AND
                        e_t::date < current_date - INTERVAL '1' day 
                 )
GROUP BY 1 
ORDER BY 3 DESC;

Upvotes: 1

Related Questions