Re: Newbie: combining two selects

From: tuxfan <blank_at_mail.box>
Date: Tue, 01 Aug 2006 16:12:23 -0400
Message-Id: <pan.2006.08.01.20.12.21.36038@mail.box>

On Mon, 31 Jul 2006 14:12:10 -0700, Martin T. wrote:

What I mean by combine is the first sql statement produces 2 columns lets call them S and C1 and the second statement produces 2 columns called S and C2. I want to combine the two selects so that the produce 3 columns C, C1 and C2. It seems that you should be able to join the sql statements as you would two oracle tables.

> tuxfan wrote:

>> I am trying to combine the following two SQL statements into one select
>> statement.  The first column in both statements are the same. It looks
>> like I need a nested statement, but I don't know how to do that.
>>
>>
>> SELECT node_data.server, COUNT ( node_data.node_name ) FROM node_data,
>> account
>> WHERE node_data.node_name = account.node_name AND acct_number = '7777'
>> GROUP BY node_data.server
>>
>> SELECT node_data.server, COUNT ( node_data.node_name ) FROM node_data,
>> account
>> WHERE node_data.node_name = account.node_name AND acct_number = '7777'
>> AND lastacct < 2
>> GROUP BY node_data.server

>
> What do you mean by "combine"?
>
> The 1st will give you some aggregate COUNT over (7777) and the 2nd will
> give you some aggregate count over (7777 && <2)
>
> You _could_ do (please someone shout at me if that's bad style):
>
> SELECT node_data.server server_1, COUNT ( node_data.node_name ) count_1,
> NULL server_2, NULL count_2
> FROM node_data, account
> WHERE node_data.node_name = account.node_name AND acct_number = '7777'
> GROUP BY node_data.server
> UNION
> SELECT NULL, NULL, node_data.server, COUNT ( node_data.node_name ) FROM
> node_data, account
> WHERE node_data.node_name = account.node_name AND acct_number = '7777'
> AND lastacct < 2
> GROUP BY node_data.server
>
> ... but then again that is probably not what you wanted?
>
> best,
> Martin
Received on Tue Aug 01 2006 - 15:12:23 CDT