Skip site navigation (1) Skip section navigation (2)

Re: Getting the count(*) from two tables and two date ranges in same query

From: Sam Mason <sam(at)samason(dot)me(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Getting the count(*) from two tables and two date ranges in same query
Date: 2008-01-28 15:18:17
Message-ID: 20080128151817.GQ18990@frubble.xen.chris-lamb.co.uk (view raw or flat)
Thread:
Lists: pgsql-general
On Mon, Jan 28, 2008 at 03:11:10PM +0100, H??kan Jacobsson wrote:
> Resulting in 4 columns in the ResultSet like:
> 
> count(*)_from_table2_between_fromdate1_and_todate1  = X
> count(*)_from_table2_between_fromdate2_and_todate2  = Y
> count(*)_from_table3_between_fromdate1_and_todate1 = Z
> count(*)_from_table3_between_fromdate2_and_todate2  = V
> 
> Is this possible?

How about something like:

  SELECT x.tbl, d.mn, COUNT(*)
  FROM (
    SELECT 'tbl2' AS tbl, id, date FROM table2
    UNION ALL
    SELECT 'tbl3', id, date FROM table3) x, (VALUES
    ('2001-1-1','2001-12-31'),
    ('2002-1-1','2002-12-31')) d(mn,mx)
  WHERE x.date BETWEEN d.mn AND d.mx
    AND x.id IN (5,6,7,8)
  GROUP BY x.tbl, d.mn
  ORDER BY x.tbl, d.mn;


  Sam

In response to

pgsql-general by date

Next:From: Rainer BauerDate: 2008-01-28 15:24:30
Subject: Re: Is news.postgresql.org down?
Previous:From: nathan wagnerDate: 2008-01-28 15:00:22
Subject: Re: Getting the count(*) from two tables and two date ranges in same query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group