Adding Two Queries Together - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding Two Queries Together

Author

Topic

MarkyC
Starting Member

4 Posts

Posted - 2014-02-07 : 08:39:36

I imagine this will be the easiest question in the world to you guys, but for me I'm finding it impossible. I know very little of SQL, and I'm basically just trying to merge two scripts into one, but I have no idea how to properly use the JOIN function, and just keep getting errors.

You'll no doubt recognise the tables, and what I'm trying to do.

Query 1;

COLUMN tablespace_name FORMAT A15
COLUMN "AVAIL" FORMAT 999,999,999,999

Select tablespace_name,
Sum(maxbytes/(1024*1024*1024)-bytes/(1024*1024*1024)) "AVAIL"
From dba_data_files
where tablespace_name like 'X%' OR tablespace_name like 'Y%'
Group By tablespace_name;

Query 2

COLUMN tablespace_name FORMAT A15
COLUMN "AVAIL" FORMAT 999,999,999,999

Select tablespace_name,
Sum(bytes/(1024*1024*1024)) "AVAIL"
From dba_free_space
where tablespace_name like 'X%' OR tablespace_name like 'Y%'
Group By tablespace_name;

Query 1 output;

TABLESPACE_NAME AVAIL
--------------- ----------------
X 8
Y 21

Query 2 ouptut;

TABLESPACE_NAME AVAIL
--------------- ----------------
X 1
Y 11

I basically want to combine the two queries so I get an output of this;

TABLESPACE_NAME AVAIL
--------------- ----------------
X 9
Y 32

I'd be grateful for any help.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-02-07 : 17:05:10

[CODE]select a.TABLESPACE_NAME, sum(a.avail) Avail
from (
<<query 1>>

UNION ALL

<<query 2>>
) a
group by a.TABLESPACE_NAME[/CODE]

===============================================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber (1894-1961)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-10 : 08:32:45

another way


select COALESCE(q1.TABLESPACE_NAME,q2.TABLESPACE_NAME) AS TABLESPACE_NAME
, COALESCE(q1.avail,0) + COALESCE(q2.Avail,0) AS  Avail
from (query1)q1
FULL JOIN (query2)q2
ON q2.TABLESPACE_NAME = q1.TABLESPACE_NAME 
GROUP BY COALESCE(q1.TABLESPACE_NAME,q2.TABLESPACE_NAME)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MarkyC
Starting Member

4 Posts

Posted - 2014-02-12 : 03:39:56

Sorry for being stupid, as I said, I know very little SQL. I can't seem to get either of these to work. Would it make a difference that this is within Oracle?

Using the first option, I tried;

select a.TABLESPACE_NAME, sum(a.avail) Avail
from (
Select tablespace_name,
Sum(maxbytes/(1024*1024*1024)-bytes/(1024*1024*1024)) "AVAIL"
From dba_data_files
where tablespace_name like 'X%' OR tablespace_name like 'Y%'
Group By tablespace_name

UNION ALL

Select tablespace_name,
Sum(bytes/(1024*1024*1024)) "AVAIL"
From dba_free_space
where tablespace_name like 'X%' OR tablespace_name like 'Y%'
Group By tablespace_name
) a
group by a.TABLESPACE_NAME

It returns the following;

SP2-0042: unknown command "UNION ALL" - rest of line ignored.

Trying the second;

select COALESCE(q1.TABLESPACE_NAME,q2.TABLESPACE_NAME) AS TABLESPACE_NAME
, COALESCE(q1.avail,0) + COALESCE(q2.Avail,0) AS Avail
from (Select tablespace_name,
Sum(maxbytes/(1024*1024*1024)-bytes/(1024*1024*1024)) "AVAIL"
From dba_data_files
where tablespace_name like 'X%' OR tablespace_name like 'Y%'
Group By tablespace_name)q1
FULL JOIN (Select tablespace_name,
Sum(bytes/(1024*1024*1024)) "AVAIL"
From dba_free_space
where tablespace_name like 'X%' OR tablespace_name like 'Y%'
Group By tablespace_name)q2
ON q2.TABLESPACE_NAME = q1.TABLESPACE_NAME
GROUP BY COALESCE(q1.TABLESPACE_NAME,q2.TABLESPACE_NAME)

It returns;

15 ;
, COALESCE(q1.avail,0) + COALESCE(q2.Avail,0) AS Avail
*
ERROR at line 2:
ORA-00979: not a GROUP BY expression

MarkyC
Starting Member

4 Posts

Posted - 2014-02-12 : 04:11:21

My mistake, it was just spacing. Massive thank you for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-12 : 05:17:08

quote:
Originally posted by MarkyC

My mistake, it was just spacing. Massive thank you for the help.

Please keep in mind that this is MS SQL Server forum
so solutions given here are guaranteed to work only on SQL Server. You may be better off trying your luck at www.dbforums.com or similar Oracle forums if you need Oracle specific help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MarkyC
Starting Member

4 Posts

Posted - 2014-02-12 : 06:13:32

No problem, I didn't know (before looking into all of this) that things would be different, but should probably have realised. Thanks again for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-12 : 07:37:46

quote:
Originally posted by MarkyC

No problem, I didn't know (before looking into all of this) that things would be different, but should probably have realised. Thanks again for the help.

No problem
you're free to post here but most of us would be able help you only with ANSI based solutions which may not always work in Oracle

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources