User Tools

Site Tools


sql_basic_stats
no way to compare when less than two revisions

Differences

This shows you the differences between two versions of the page.


sql_basic_stats [2015/11/18 04:58] (current) – created daniel
Line 1: Line 1:
 +====== Number of messages for a given month ======
 +(including days with no message).
 +Example for July, 2015
 +<code postgresql>
 +
 +SELECT day::date, coalesce(cnt,0) as cnt FROM
 + (select date_trunc('day',msg_date) as day, count(*) as cnt
 +  from mail 
 +  where msg_date>='2015-07-01'::timestamptz
 +    and msg_date<'2015-07-01'::timestamptz+interval '1 month'
 +    -- and other optional conditions
 +  group by 1
 + ) as list
 +RIGHT JOIN
 +   generate_series('2015-07-01'::timestamptz,
 +                   '2015-07-01'::timestamptz+interval '1 month'-interval '1 day',
 +                    interval '1 day') as d(day)
 +   USING (day)
 +ORDER BY 1 
 +</code>
 +
  
sql_basic_stats.txt · Last modified: 2015/11/18 04:58 by daniel