Combing Query Results - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Combing Query Results

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-09-19 : 16:31:38

Hey all,

I am new to the SQL world. I have been put on a project that requires me to build some specialized SQL querys.

I am attempting to Select data from two different Tables. I simply want to look at the total of both tables as one result.
Here is what I had for code:
SELECT Sum (Chem1)FROM Precip1Production
UNION ALL
SELECT Sum (Chem1)FROM Precip2Production

Example results are:

1000
900

The result I am looking for is

1900.

Can anyone help me know what code i need to SUM these query results?

Any help would be greatly appreciated.

Thanks,

John

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-09-19 : 16:38:29

select sum(chem1) from (
SELECT Sum (Chem1)chem1 FROM Precip1Production
UNION ALL
SELECT Sum (Chem1)FROM Precip2Production ) s

or

select sum(chem1) from (
select chem1 from Precip1Production
UNION ALL
SELECT Chem1FROM Precip2Production ) s

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-09-20 : 07:42:59

Thanks that did it! What is the "s" at the end of this statement?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-20 : 07:52:12

Thats an alias for the derived table ... you have to name the derived table something ...

Jay White
{0}

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-09-20 : 08:31:08

Ok, so now I want to complicate things a little and add a WHERE statement. Obviously this is coming from a produiction database. Many times they will search by criteria. Under normal circumstances the WHERE statements are easy to set up. With the alias I am a little confused.... Heres what I have:

SUM (Polymin), SUM (SodAlum), SUM (AMX), SUM (Kymene)
FROM (select Chem1, Chem2, Chem3, Chem4, Chem5, Chem6, Alum, Phenolic, Fiber1, Fiber2,
Latex1, Latex2, CarbonBlack, Darvan, Caustic, Coagulant, Hot_Water, Wash_Water, ManualAdd1,
ManualAdd2, ManualAdd3, HardwoodPulp, Polymin, SodAlum, AMX, Kymene FROM Precip1Production
UNION ALL
SELECT Chem1, Chem2, Chem3, Chem4, Chem5, Chem6, Alum, Phenolic, Fiber1, Fiber2,
Latex1, Latex2, CarbonBlack, Darvan, Caustic, Coagulant, Hot_Water, Wash_Water, ManualAdd1,
ManualAdd2, ManualAdd3, HardwoodPulp, Polymin, SodAlum, AMX, Kymene FROM Precip2Production) s
WHERE Precip1Production.Recipe = 'xxxx'

Im sure I am supposed to include the alias in here but not sure on the syntax.

John

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-09-20 : 08:51:35

SUM (Polymin), SUM (SodAlum), SUM (AMX), SUM (Kymene)
FROM (select Chem1, Chem2, Chem3, Chem4, Chem5, Chem6, Alum, Phenolic, Fiber1, Fiber2,
Latex1, Latex2, CarbonBlack, Darvan, Caustic, Coagulant, Hot_Water, Wash_Water, ManualAdd1,
ManualAdd2, ManualAdd3, HardwoodPulp, Polymin, SodAlum, AMX, Kymene,recipe FROM Precip1Production
UNION ALL
SELECT Chem1, Chem2, Chem3, Chem4, Chem5, Chem6, Alum, Phenolic, Fiber1, Fiber2,
Latex1, Latex2, CarbonBlack, Darvan, Caustic, Coagulant, Hot_Water, Wash_Water, ManualAdd1,
ManualAdd2, ManualAdd3, HardwoodPulp, Polymin, SodAlum, AMX, Kymene ,recipe FROM Precip2Production) s
WHERE s.Recipe = 'xxxx'

Note that i included recipe in each of the union statements.

Or you could put the restriction in each of the unions.

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-09-20 : 09:16:15

Thanks alot! You were a tremendous help.

John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources