Thread: Help for construct Query

Hi all,

I have the following two tables, the table correo have the messages send
using the mail, the other table web have the message send using web.

I have the following query:

select SUBSTR(hora,1,2) as ho,count(*) as tot from web as a where
fecha='11/20/03' group by SUBSTR(hora,1,2);

select SUBSTR(hora,1,2) as ho,count(*) as tot from correo as a where
fecha='11/20/03' group by SUBSTR(hora,1,2);

my result is:

 hour | messages
------+---------
 00 |  35
 01 |   9
 02 |   4
 04 |   1
 05 |   9
 06 |  42
 07 | 132
 08 | 446
 09 | 505

But I need have only one query for both tables, I was trying but I can't,
how can I do it?

Thanks in Advanced.

> I have the following two tables, the table correo have the messages send
> using the mail, the other table web have the message send using web.
[snip]
> But I need have only one query for both tables, I was trying but I can't,
> how can I do it?

Have you looked into UNION?

Also, are the tables indentical in all but name?  If so, perhaps consolidating
the two tables and add a field to be used to distinguish between the two types
of messages?

Chris

Him Thanks for you answer, at this moment I can't consilidate the two
tables, we are redisign the system, but at this moment I need this datas for
a report.

I used UNION, but I have the problem that the count(*) when not found rows
no print the the hour, If my the hour 3 AM no have rows I need that this
hour have 0.

Example
Results
===========
hour | messages
------+---------
 00 |  35
 01 |   9
 02 |   4
 04 |   1
 05 |   9
 06 |  42
 07 | 132
 08 | 446
 09 | 505

I need
=======
hour | messages
------+---------
 00 |  35
 01 |   9
 02 |   4
 03 |  0
 04 |   1
 05 |   9
 06 |  42
 07 | 132
 08 | 446
 09 | 505


----- Original Message -----
From: "Chris Boget" <chris@wild.net>
To: "Viaris hotmail" <viaris@hotmail.com>; <pgsql-novice@postgresql.org>
Sent: Friday, November 21, 2003 9:30 AM
Subject: Re: [NOVICE] Help for construct Query


> > I have the following two tables, the table correo have the messages send
> > using the mail, the other table web have the message send using web.
> [snip]
> > But I need have only one query for both tables, I was trying but I
can't,
> > how can I do it?
>
> Have you looked into UNION?
>
> Also, are the tables indentical in all but name?  If so, perhaps
consolidating
> the two tables and add a field to be used to distinguish between the two
types
> of messages?
>
> Chris
>
>