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

Re: Select statement problem

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: PostgreSQL List - Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Select statement problem
Date: 2010-10-08 10:45:13
Message-ID: BLU0-SMTP740980A97BA348D209C13AAC500@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-novice
Lukasz Brodziak wrote:
> Hello,
> 
> I know the question may appear lame but I have problem with a SELECT
> statement for a report. I have two separate statements:
> SELECT t1.col1, t1.col2, SUM(t2.end_date::DATE - t2.start_date::DATE)
>    from table1 t1, table2 t2, table3 t3 WHERE t1.col3 = t2.col3 and
> t3.col4 = t1.col4;
> 
> SELECT (Now()::DATE - t2.start_date::DATE) AS days
>    from table1 t1, table2 t2, table3 t3 WHERE t1.col3 = t2.col3 and
> t3.col4 = t1.col4 and t2.end_date is null;
> 
> What I need is to have both statements' results presented in one table
> which has 4 columns (t1.col1, t1.col2, "SUM", days). The tables used
> are the same in both statements.


It is not clear how these two queries should be joined; but you want to 
use something like this...

SELECT sql1.col1, sql1.col2, sql1.sum, sql2.days from (

SELECT t1.col3, t1.col4, t1.col1, t1.col2,
    SUM(t2.end_date::DATE - t2.start_date::DATE) as sum
    from table1 t1, table2 t2, table3 t3 WHERE t1.col3 = t2.col3 and
    t3.col4 = t1.col4

) sql1 left join (

SELECT t1.col3, t1.col4, t1.col1, t1.col2,
    (Now()::DATE - t2.start_date::DATE) AS days
    from table1 t1, table2 t2, table3 t3 WHERE t1.col3 = t2.col3 and
    t3.col4 = t1.col4 and t2.end_date is null

) sql2 on sql1.col3 = sql2.col3 and sql1.col4 = sql2.col4

Frank


In response to

Responses

pgsql-novice by date

Next:From: Mladen GogalaDate: 2010-10-08 12:14:31
Subject: Re: Asynchronous I/O in Postgres
Previous:From: Lukasz BrodziakDate: 2010-10-08 05:41:14
Subject: Select statement problem

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