Re: Getting the count(*) from two tables and two date ranges in same query - Mailing list 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