Adding results from 2 select statements

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

Transact-SQL (2000)

Adding results from 2 select statements

Author

Topic

lpetrill
Starting Member

5 Posts

Posted - 2004-02-20 : 08:27:40

Sorry, if this sounds childish but I can't figure it out, say I run 2 select statements and I receive of course 2 different results how can I add both results into one.

exp.

select count(*) from table1 union select count(*) from table2

instead of 2 individual results I just need the total. Again sorry if this sounds easy but I'm fairly new at this.

Thanks

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-02-20 : 08:33:25

How about

SELECT SUM(tablerows) FROM
(
SELECT tablerows = COUNT(*) FROM table1
UNION
SELECT COUNT(*) from table2
)

Raymond

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-20 : 08:42:21

SELECT COUNT(*)
FROM (
  SELECT MyColumn
    FROM TABLE1
  UNION ALL
  SELECT MyOtherColumn
    FROM TABLE2
  ) X

lpetrill
Starting Member

5 Posts

Posted - 2004-02-20 : 11:27:24

thanks for the previous replies but I'm unable to get it to work, maybe if I post the actually queries you might make sense of it. Thanks

select count(*) as 'Non-TIF objects' from iadocument where deleteflag <> '1' and iastoragehandle is null and filetype <> 'CLD' and filetype <> 'TIF' and oh like 'PFF0001 %'
union select count(*) as 'TIF objects' from iapage where deleteflag <> '1' and iastoragehandle is null and oh like 'PFF0001 %'

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-02-20 : 11:34:12

I don't see the code from either myself or Sam. Have you tried surrounding your select statements with either of the above examples?

Raymond

lpetrill
Starting Member

5 Posts

Posted - 2004-02-23 : 09:20:12

i have tried sorrounding it with different statements, But I keep getting syntax erros, I'm new at this if either one of you would help me out with the actual query that I have posted I would greatly appreciate it. Thaks

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-02-23 : 09:27:19

I've given you what I consider to be the answer in my first post. Substitute 'table1' and 'table2' for your actual table names, or try Sam's doing the same thing.

Raymond

lpetrill
Starting Member

5 Posts

Posted - 2004-02-23 : 09:47:09

Sorry to be such a pest, here's the way I'm running the query and the results:

SELECT SUM(tablerows) FROM ( SELECT tablerows = COUNT(*) FROM iadocument where deleteflag <> '1' and iastoragehandle is null and filetype <> 'CLD' and filetype <> 'TIF' and oh like 'SB0002 %'
UNION
SELECT COUNT(*) from iapage where deleteflag <> '1' and iastoragehandle is null and oh like 'SB0002 %')

Res: Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ')'.

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-02-23 : 09:55:22

Apologies, the error was mine in my my first answer. The derived table needs to be named much like Sam did with his - put an 'X' after the last bracket. Damn cut and paste ....

Raymond

lpetrill
Starting Member

5 Posts

Posted - 2004-02-23 : 10:24:33

Apologies are mine for being such a pest, but it worked and I thank you and Sam for all the help and putting up with my idiocy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources