User Tools

Site Tools


sql_basic_stats

Differences

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

Link to this comparison view

sql_basic_stats [2015/11/18 05:58] (current)
daniel created
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 05:58 by daniel