adding two computed columns in single query

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 two computed columns in single query

Author

Topic

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2007-03-26 : 13:03:44

I'm sure this has been asked a million times, but my searches were unsuccesful. If I have a SELECT statement that returns three columns, the first two computed with SUM and the third computed by dividing the first column by the second column, is there a way to do that in a single query? Do I need a temp table?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-26 : 13:08:45

[code]
select col1 = sum(cola), col2 = sum(colb), col3 = sum(cola) / sum(colb)
from sometable
[/code]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-26 : 13:09:36

select col1, ComputedColumn1/col1 as ComputedColumn3
from
(
select col1, col2 as ComputedColumn1
from myTable
) t

the thing you want to achieve is also called an average if i'm understanding you correctly.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2007-03-26 : 13:16:40

This is the kinda thing I'm looking for.

SELECT totalMonkeys = SUM(CASE WHEN colA = 'monkey' THEN 1 ELSE 0 END),
totalPrimates = SUM(CASE WHEN colA IS NOT NULL THEN 1 ELSE 0 END),
percentMonkeys = totalMonkeys/totalPrimates
FROM tableA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-26 : 13:25:21

[code]
SELECT totalMonkeys = SUM(CASE WHEN colA = 'monkey' THEN 1 ELSE 0 END),
totalPrimates = SUM(CASE WHEN colA IS NOT NULL THEN 1 ELSE 0 END),
percentMonkeys = SUM(CASE WHEN colA = 'monkey' THEN 1 ELSE 0 END) / SUM(CASE WHEN colA IS NOT NULL THEN 1 ELSE 0 END)
FROM tableA

OR

SELECT totalMonkeys, totalPrimates, percentMonkeys = totalMonkeys / totalPrimates
FROM
(
SELECT totalMonkeys = SUM(CASE WHEN colA = 'monkey' THEN 1 ELSE 0 END),
totalPrimates = SUM(CASE WHEN colA IS NOT NULL THEN 1 ELSE 0 END)
FROM tableA
) a
[/code]

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-26 : 13:30:53

On 2000 you have a few options:

Option 1 - repeat the calculation
SELECT totalMonkeys = SUM(CASE WHEN colA = 'monkey' THEN 1 ELSE 0 END),
totalPrimates = SUM(CASE WHEN colA IS NOT NULL THEN 1 ELSE 0 END),
percentMonkeys = SUM(CASE WHEN colA = 'monkey' THEN 1 ELSE 0 END)/SUM(CASE WHEN colA IS NOT NULL THEN 1 ELSE 0 END)
FROM tableA

Option 2 - use a subquery
SELECT totalMonkeys, totalPrimates, percentMonkeys = totalMonkeys/totalPrimates
FROM (SELECT totalMonkeys = SUM(CASE WHEN colA = 'monkey' THEN 1 ELSE 0 END),
totalPrimates = SUM(CASE WHEN colA IS NOT NULL THEN 1 ELSE 0 END)
FROM tableA) AS A

Option 3 - use a view
CREATE VIEW MonkeyTotals
AS
SELECT totalMonkeys = SUM(CASE WHEN colA = 'monkey' THEN 1 ELSE 0 END),
totalPrimates = SUM(CASE WHEN colA IS NOT NULL THEN 1 ELSE 0 END)
FROM tableA
Then select from the view in your apps
SELECT totalMonkeys, totalPrimates, percentMonkeys = totalMonkeys/totalPrimates
FROM MonkeyTotals

On 2005 you can use a CTE instead of these options.

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2007-03-26 : 13:33:26

Thanks KH, I might have to go with your second option, since I'm assuming your first query will repeat each CASE lookup twice.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-26 : 13:46:57

quote:
Originally posted by influent

Thanks KH, I might have to go with your second option, since I'm assuming your first query will repeat each CASE lookup twice.

yes it does

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2007-03-26 : 16:39:57

I take it back, it repeats each CASE lookup once (I was being redundant), but you knew what I meant. Thanks all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources