Обсуждение: Getting the count(*) from two tables and two date ranges in same query

> 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?


Select t1.id,
sum(case when t2.date between d1 and d2 then 1 else 0 end) as sum1,
sum(case when t3.date between d1 and d2 then 1 else 0 end) as sum2
from t1, t2, t3
where t1.id=t2.id and t2.id = t3.id
group by t1.id

Adam Rich 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?
>
>
> Select t1.id,
> sum(case when t2.date between d1 and d2 then 1 else 0 end) as sum1,
> sum(case when t3.date between d1 and d2 then 1 else 0 end) as sum2
> from t1, t2, t3
> where t1.id=t2.id and t2.id = t3.id
> group by t1.id

My first thought would be to use a subselect.

select
   (select count(table1_ID) from t2
     where date > fromdate1 and date < todate1)) as X,
   (select count(table1_ID) from t2
     where date > fromdate2 and date < todate2)) as Y,
   (select count(table1_ID) from t3
     where date > fromdate1 and date < todate1)) as Z,
   (select count(table1_ID) from t3
     where date > fromdate2 and date < todate2)) as V
;

No idea if that's the most efficient, but it is more
intuitive to me.  I hadn't really been aware of 'between'.

from
http://www.postgresql.org/docs/8.2/interactive/functions-comparison.html

it seems that

"a BETWEEN x AND y

is equivalent to

a >= x AND a <= y"

Which is wrong (though it may be required by the standard, of course).

1 is not between 1 and 2.  "between" shouldn't include the endpoints.

At any rate, the OP will know what he meant by "between" and can select
the appropriate operators.

--
nathan wagner
nw@hydaspes.if.org

Hi Adam and all,

I don't get it=). How do I input the second daterange in this query?
Also, I have the ID from table1. Its known in the query.

Wouldn't I need to use a UNION for this kind of query?

Håkan Jacobsson - System Developer
----------------------------------------------------------------

RELEVANT TRAFFIC EUROPE AB, Riddarg 17D, SE-114 57 Sthlm, Sweden

Mobile (+46) 736 56 97 58
Direct   (+46) 8 56 24 98 05
Phone to office (+46) 8 678 97 50 || Fax (+46) 8 661 19 22


-----Ursprungligt meddelande-----
Från: Adam Rich [mailto:adam.r@sbcglobal.net]
Skickat: den 28 januari 2008 15:22
Till: Håkan Jacobsson; pgsql-general@postgresql.org
Ämne: RE: [GENERAL] Getting the count(*) from two tables and two date ranges in same query

> 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?


Select t1.id,
sum(case when t2.date between d1 and d2 then 1 else 0 end) as sum1,
sum(case when t3.date between d1 and d2 then 1 else 0 end) as sum2
from t1, t2, t3
where t1.id=t2.id and t2.id = t3.id
group by t1.id

Håkan Jacobsson wrote:

  > I don't get it=). How do I input the second daterange in this query?

Through whatever mechanism you are using to issue the query.

 > Also, I have the ID from table1. Its known in the query.

Oops.  I forgot that part in my reply.  So my where clause is
wrong, though easily modified to add "and table1_id = foo".

 > Wouldn't I need to use a UNION for this kind of query?

A union will combine rows (if they're compatible) from two queries,
What you are asking is to assemble columns, which is entirely different.

--
nathan wagner
nw@hydaspes.if.org
Please don't top-post.

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

Adam,

Big thanx - I'll try this out!

Håkan Jacobsson - System Developer
----------------------------------------------------------------

RELEVANT TRAFFIC EUROPE AB, Riddarg 17D, SE-114 57 Sthlm, Sweden

Mobile (+46) 736 56 97 58
Direct   (+46) 8 56 24 98 05
Phone to office (+46) 8 678 97 50 || Fax (+46) 8 661 19 22


-----Ursprungligt meddelande-----
Från: Adam Rich [mailto:adam.r@sbcglobal.net]
Skickat: den 28 januari 2008 16:27
Till: Håkan Jacobsson; pgsql-general@postgresql.org
Ämne: Re: [GENERAL] Getting the count(*) from two tables and two date ranges in same query


Håkan,
You can add as many date ranges as you need:

Select t1.id,
sum(case when t2.date between d1 and d2 then 1 else 0
end) as sum1,
sum(case when t2.date between d3 and d4 then 1 else 0
end) as sum2
sum(case when t3.date between d1 and d2 then 1 else 0
end) as sum3,
sum(case when t3.date between d3 and d4 then 1 else 0
end) as sum4
from t1, t2, t3
where t1.id=t2.id and t2.id = t3.id
group by t1.id

If you know the ID, you can modify slightly:


Select
sum(case when t2.date between d1 and d2 then 1 else 0
end) as sum1,
sum(case when t2.date between d3 and d4 then 1 else 0
end) as sum2
sum(case when t3.date between d1 and d2 then 1 else 0
end) as sum3,
sum(case when t3.date between d3 and d4 then 1 else 0
end) as sum4
from t2, t3
where t2.id = t3.id
and t2.id = 123456



--- Håkan Jacobsson
<hakan.jacobsson@relevanttraffic.com> wrote:

> Hi Adam and all,
>
> I don't get it=). How do I input the second
> daterange in this query?
> Also, I have the ID from table1. Its known in the
> query.
>
> Wouldn't I need to use a UNION for this kind of
> query?
>
> Håkan Jacobsson - System Developer
>
----------------------------------------------------------------
>
> RELEVANT TRAFFIC EUROPE AB, Riddarg 17D, SE-114 57
> Sthlm, Sweden
>
> Mobile (+46) 736 56 97 58
> Direct   (+46) 8 56 24 98 05
> Phone to office (+46) 8 678 97 50 || Fax (+46) 8 661
> 19 22
>
>
> -----Ursprungligt meddelande-----
> Från: Adam Rich [mailto:adam.r@sbcglobal.net]
> Skickat: den 28 januari 2008 15:22
> Till: Håkan Jacobsson; pgsql-general@postgresql.org
> Ämne: RE: [GENERAL] Getting the count(*) from two
> tables and two date ranges in same query
>
> > 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?
>
>
> Select t1.id,
> sum(case when t2.date between d1 and d2 then 1 else
> 0 end) as sum1,
> sum(case when t3.date between d1 and d2 then 1 else
> 0 end) as sum2
> from t1, t2, t3
> where t1.id=t2.id and t2.id = t3.id
> group by t1.id
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will
> ignore your desire to
>        choose an index scan if your joining column's
> datatypes do not
>        match
>

Adam,

I don't get the correct row counts when running this SQL.
It seems to produce the correct count when there are no rows found,but not when at least
one row is found (I get a much higher count than when running:

SELECT count(*) FROM table2 WHERE date BETWEEN.....AND table1.id = n

,which is the count I want.)

Might be some problem with the case statement? Or with the

from table2, table3
where table2.table1id = table3.table1id

part?

Very grateful for help!


Håkan Jacobsson - System Developer
----------------------------------------------------------------

RELEVANT TRAFFIC EUROPE AB, Riddarg 17D, SE-114 57 Sthlm, Sweden

Mobile (+46) 736 56 97 58
Direct   (+46) 8 56 24 98 05
Phone to office (+46) 8 678 97 50 || Fax (+46) 8 661 19 22


-----Ursprungligt meddelande-----
Från: Adam Rich [mailto:adam.r@sbcglobal.net]
Skickat: den 28 januari 2008 16:27
Till: Håkan Jacobsson; pgsql-general@postgresql.org
Ämne: Re: [GENERAL] Getting the count(*) from two tables and two date ranges in same query


Håkan,
You can add as many date ranges as you need:

Select t1.id,
sum(case when t2.date between d1 and d2 then 1 else 0
end) as sum1,
sum(case when t2.date between d3 and d4 then 1 else 0
end) as sum2
sum(case when t3.date between d1 and d2 then 1 else 0
end) as sum3,
sum(case when t3.date between d3 and d4 then 1 else 0
end) as sum4
from t1, t2, t3
where t1.id=t2.id and t2.id = t3.id
group by t1.id

If you know the ID, you can modify slightly:


Select
sum(case when t2.date between d1 and d2 then 1 else 0
end) as sum1,
sum(case when t2.date between d3 and d4 then 1 else 0
end) as sum2
sum(case when t3.date between d1 and d2 then 1 else 0
end) as sum3,
sum(case when t3.date between d3 and d4 then 1 else 0
end) as sum4
from t2, t3
where t2.id = t3.id
and t2.id = 123456



--- Håkan Jacobsson
<hakan.jacobsson@relevanttraffic.com> wrote:

> Hi Adam and all,
>
> I don't get it=). How do I input the second
> daterange in this query?
> Also, I have the ID from table1. Its known in the
> query.
>
> Wouldn't I need to use a UNION for this kind of
> query?
>
> Håkan Jacobsson - System Developer
>
----------------------------------------------------------------
>
> RELEVANT TRAFFIC EUROPE AB, Riddarg 17D, SE-114 57
> Sthlm, Sweden
>
> Mobile (+46) 736 56 97 58
> Direct   (+46) 8 56 24 98 05
> Phone to office (+46) 8 678 97 50 || Fax (+46) 8 661
> 19 22
>
>
> -----Ursprungligt meddelande-----
> Från: Adam Rich [mailto:adam.r@sbcglobal.net]
> Skickat: den 28 januari 2008 15:22
> Till: Håkan Jacobsson; pgsql-general@postgresql.org
> Ämne: RE: [GENERAL] Getting the count(*) from two
> tables and two date ranges in same query
>
> > 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?
>
>
> Select t1.id,
> sum(case when t2.date between d1 and d2 then 1 else
> 0 end) as sum1,
> sum(case when t3.date between d1 and d2 then 1 else
> 0 end) as sum2
> from t1, t2, t3
> where t1.id=t2.id and t2.id = t3.id
> group by t1.id
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will
> ignore your desire to
>        choose an index scan if your joining column's
> datatypes do not
>        match
>

Adam,

I just realised that issuing the SQL on one table produces the correct count.

SELECT sum(case when table2.date between '2007-07-13' and '2007-07-13' then 1 else 0
end) as sumx FROM table2 WHERE id = n;

This is working alright.
So the problem should lie in the last part:

from table2, table3
where table2.id = table3.id
and table2.id = n;

I think I need the UNION statement instead?

Håkan Jacobsson - System Developer
----------------------------------------------------------------

RELEVANT TRAFFIC EUROPE AB, Riddarg 17D, SE-114 57 Sthlm, Sweden

Mobile (+46) 736 56 97 58
Direct   (+46) 8 56 24 98 05
Phone to office (+46) 8 678 97 50 || Fax (+46) 8 661 19 22

=?iso-8859-1?Q?H=E5kan_Jacobsson?= <hakan.jacobsson@relevanttraffic.com> writes:
> I just realised that issuing the SQL on one table produces the correct count.

> SELECT sum(case when table2.date between '2007-07-13' and '2007-07-13' then 1 else 0
> end) as sumx FROM table2 WHERE id = n;

> This is working alright.
> So the problem should lie in the last part:

> from table2, table3
> where table2.id = table3.id
> and table2.id = n;

It sounds like there are multiple rows in table3 matching the id of (at
least some of) the rows in table2.  You'll get an increment to the sum
for each join pair.

            regards, tom lane

On Wed, Jan 30, 2008 at 09:52:17AM +0100, H??kan Jacobsson wrote:
> I just realised that issuing the SQL on one table produces the correct count.
>
> SELECT sum(case when table2.date between '2007-07-13' and '2007-07-13' then 1 else 0
> end) as sumx FROM table2 WHERE id = n;
>
> This is working alright.
> So the problem should lie in the last part:
>
> from table2, table3
> where table2.id = table3.id
> and table2.id = n;
>
> I think I need the UNION statement instead?

Did you try something similar to the code I posted?


  Sam