WritingAComplexQuery - Babase Wiki

Write a query to count the number of ranked males per month in each group.

{{{select malegroupmonth.grp, malegroupmonth.date, count(*)

from (select distinct
- members.sname as sname
- , members.grp as grp , date_trunc('month', members.date)::date as date
- from biograph
  - join members on (members.sname = biograph.sname) join rankdates on (rankdates.sname = biograph.sname)
  where biograph.sex = 'M'
  - -- Date_trunc on rankdates.ranked -- is totally unnecessary -- but how can I resist showing off my sql-fu? and date_trunc('month', members.date)
    <= date_trunc('month', rankdates.ranked))
- as malegroupmonth
group by malegroupmonth.grp, malegroupmonth.date order by malegroupmonth.grp, malegroupmonth.date;

}}}

Wiki content based upon work supported by the National Science Foundation under Grant Nos. 0323553 and 0323596. Any opinions, findings, conclusions or recommendations expressed in this material are those of the wiki contributor(s) and do not necessarily reflect the views of the National Science Foundation.