Problem with "select a, b, a+b from table" in SQL Server 2000

Using SQL Server 2000, I'm having a problem trying to derive a query field
from two other fields returned in the same query.
select
[User - Name] = user.name,
[User - ID] = user.id,
[Num Laptops] = (select sum(...) where id = user.id ),
[Num PCs] = (select sum(...) where id = user.id ),
[Total Computers] = [Num Laptops] + [Num PCs],
...
The erroneous line is the [Total Computers] line.
If I remove that, the query works fine and correctly returns the number of
laptops and pcs for each user.
How can I derive this field from the other fields returned in the query?
Any help most appreciated.
Mr Nice#laptops and #pc are calculated values that only exist at runtime. Thus,
they're not available for you to reference in the select to calc #total.
However, you can derive the whle select and the use the #laptops and #pc.
e.g.
select *,lp+pc
from (
select
[User - Name] = user.name,
[User - ID] = user.id,
[Num Laptops] = (select sum(...) where id = user.id ),
[Num PCs] = (select sum(...) where id = user.id )
from ...
where ..
) as derived
-oj
"Mr Nice" <no.spam@.thanks> wrote in message
news:420c6897$0$21947$cc9e4d1f@.news.dial.pipex.com...
> Using SQL Server 2000, I'm having a problem trying to derive a query field
> from two other fields returned in the same query.
>
> select
> [User - Name] = user.name,
> [User - ID] = user.id,
> [Num Laptops] = (select sum(...) where id = user.id ),
> [Num PCs] = (select sum(...) where id = user.id ),
> [Total Computers] = [Num Laptops] + [Num PCs],
> ...
> The erroneous line is the [Total Computers] line.
> If I remove that, the query works fine and correctly returns the number of
> laptops and pcs for each user.
> How can I derive this field from the other fields returned in the query?
> Any help most appreciated.
> Mr Nice
>|||Mr Nice wrote:
> select
> [User - Name] = user.name,
> [User - ID] = user.id,
> [Num Laptops] = (select sum(...) where id = user.id ),
> [Num PCs] = (select sum(...) where id = user.id ),
> [Total Computers] = [Num Laptops] + [Num PCs],
SELECT
[User - Name] = user.name,
[User - ID] = user.id,
[Num Laptops] = (select sum(...) where id = user.id ),
[Num PCs] = (select sum(...) where id = user.id ),
[Total Computers] = (select sum(...) where id = user.id ) + (select
sum(...) where id = user.id ),
However I'm sure that there is a way to do it without using as many
sub-queries. But that's my own personal pet peeve (I dislike subqueries
for some reason, that and in this case it loooks excessive).
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||Please include DDL with your posts otherwise we can only guess what
your tables look like.
Given:
CREATE TABLE Users (user_id INTEGER NOT NULL PRIMARY KEY, user_name
VARCHAR(50) NOT NULL UNIQUE)
CREATE TABLE Assets (asset_number VARCHAR(20) NOT NULL PRIMARY KEY,
asset_type CHAR(2) NOT NULL CHECK (asset_type IN ('PC','LT')), user_id
INTEGER NOT NULL REFERENCES Users (user_id))
You could do this:
SELECT U.user_id, U.user_name,
COUNT(CASE WHEN asset_type = 'LT' THEN 1 END) AS num_laptops,
COUNT(CASE WHEN asset_type = 'PC' THEN 1 END) AS num_pcs
FROM Users AS U
LEFT JOIN Assets AS H
ON U.user_id = H.user_id
GROUP BY U.user_id, U.user_name
David Portas
SQL Server MVP
--