Multiple selects to one table - 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

Multiple selects to one table

Author

Topic

kevin.fitzpatrick
Starting Member

5 Posts

Posted - 2007-06-01 : 16:20:28

Hello, i would like to take the following select statements and make one table out of them. However, i want each statement to be its own column. The Union adds all of the statements into one colummn. Ultimately, i would like to place these into a stored procedure. Any help would be apreciated. Here are the statements:

select count(chalf_upd) as CHalfFile from elpc where elpc.chalf_upd is not null

select count (halfupd) as HalfFile from elpc where elpc.halfupd is not null

select count (cstaff_upd) as CStaffFile from elpc where elpc.cstaff_upd is not null

select count (staffupd) as StaffFile from elpc where elpc.staffupd is not null

select count (coffice_up) as COfficeFile from elpc where elpc.coffice_up is not null

select count (officeupd) as OfficeFile from elpc where elpc.officeupd is not null

select count (signupd) as SignFile from elpc where elpc.signupd is not null

select count (informupd) as ISignFile from elpc where elpc.informupd is not null

select count(staff_color_lastupd) as InStaffFile from staff_graphics_lastupdate where staff_graphics_lastupdate.staff_color_lastupd is not null

Strange Game. The only winnng move is not to play.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-01 : 19:48:01

[code]select
(select count(chalf_upd) as CHalfFile from elpc where elpc.chalf_upd is not null) as CHalfFile
(select count(halfupd) as HalfFile from elpc where elpc.halfupd is not null) as HalfFile
(select count(cstaff_upd) as CStaffFile from elpc where elpc.cstaff_upd is not null) as CStaffFile
(select count(staffupd) as StaffFile from elpc where elpc.staffupd is not null) as StaffFile
(select count(coffice_up) as COfficeFile from elpc where elpc.coffice_up is not null) as COfficeFile
(select count(officeupd) as OfficeFile from elpc where elpc.officeupd is not null) as OfficeFile
(select count(signupd) as SignFile from elpc where elpc.signupd is not null) as SignFile
(select count(informupd) as ISignFile from elpc where elpc.informupd is not null) as ISignFile
(select count(staff_color_lastupd) as InStaffFile from staff_graphics_lastupdate where staff_graphics_lastupdate.staff_color_lastupd is not null) as InStaffFile[/code]

quote:
Originally posted by kevin.fitzpatrick
Strange Game. The only winnng move is not to play.

"How about a nice game of chess?" --There's no such thing as a nice game of chess...

e4 d5 xd5 Nf6

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-02 : 03:07:33

Also, your count(*) from elpc table can be written as


Select
      sum(case when chalf_upd is not null then 1 else 0 end) as CHalfFile,
      sum(case when halfupd is not null then 1 else 0 end) as HalfFile,
      .
      .
from
      elpc

Madhivanan

Failing to plan is Planning to fail

kevin.fitzpatrick
Starting Member

5 Posts

Posted - 2007-06-04 : 12:26:30

Thanks...Works Nicely.

**Strange Game. The only winnng move is not to play.**

kevin.fitzpatrick
Starting Member

5 Posts

Posted - 2007-06-04 : 17:21:20

is there anyway to make this faster? It seems extremely slow. I have a table of about 20k records, and when this is plugged into .net front end, the page takes 30+ seconds to load.j

**Strange Game. The only winnng move is not to play.**

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-04 : 22:49:22

Not easily. You are performing 9 separate queries against the table.
Madhivanan's suggestion should reduce this to a single scan of the table, which may save you some execution time.

e4 d5 xd5 Nf6

kevin.fitzpatrick
Starting Member

5 Posts

Posted - 2007-06-05 : 10:10:18

The sum(case) technique improves execution speeds greatly.

Thank you much.

**Strange Game. The only winnng move is not to play.**

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources