Use select fields for building another select field

Question

Post reply

Use select fields for building another select field

Dinculescu Mihai

Old Hand

Points: 327
More actions
June 30, 2007 at 11:18 am

#177314

I would like to know if its possible to do this, and if it is, how exactly.
SELECT (SELECT COUNT(*) FROM TABLE1) AS NUMBER1,
(SELECT COUNT(*) FROM TABLE2) AS NUMBER2,
(NUMBER1 + NUMBER2) AS Total
Currently I'm getting "Invalid column name 'NUMBER1'" and "Invalid column name 'NUMBER2'" errors.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

michaela SSCertifiable Points: 5649 More actions · Answer 1

declare

@NUMBER1 int,

@NUMBER2

int,

@NUMBER3

int

SELECT

@NUMBER1=(select COUNT(*) from TABLE1),@NUMBER2=(select COUNT(*) from TABLE2),

@NUMBER3

=@NUMBER1 + @NUMBER2

select

@NUMBER1,@NUMBER2,@NUMBER3

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Or, without variables...

 SELECT d.Number1,
        d.Number2,
        (d.Number1 + d.Number2) AS Number3
   FROM (--==== Derived table "d" does the aggragates once
             -- so outer select can reuse them with ease
         SELECT (SELECT COUNT(*) FROM Table1) AS Number1,
                (SELECT COUNT(*) FROM Table2) AS Number2
        ) d

Method is very similar to how a CTE would be used in SQL Server 2005... I don't have 2005 to test with so I posted the SQL Server 2000 solution...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dinculescu Mihai Old Hand Points: 327 More actions · Answer 3

Yes, thats what I did. I created a derived table and populated it with the initial data and then I've used Update on it to calculate the other fields.

Thanks for your replies.