SQL Calculations / Possible Subquery

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

SQL Calculations / Possible Subquery

Author

Topic

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-04-29 : 10:59:42

Hi all. Newbie here. Have a question for you all.

I'm trying to create a SQL query that will perform a calculation. Here's my handwritten calculation...

Total = (# of primarys * 1) + (# of secondarys * .5)
Primarys = select count(id) from tbl where criteria
Secondary = select count(id) from tbl where criteria2

Example...
Declare @primary int
Declare @secondary decimal (10,1)
Set @primary=1
Set @secondary=.5

Select count(id) * @primary from tbl where criteria
+
Select count(id) * @secondary from tbl where othercriteria

I just don't know how to create the SQL statement because once I state "From" I don't think I can do another "Select".

Does that make sense?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-29 : 11:03:28

[code]
select count(case when criteria then id else null end) as Primarys,
count(case when criteria2 then id else null end) as Secondary,
count(case when criteria then id else null end) + count(case when criteria2 then id else null end) *0.5 as Total
from tbl
[/code]

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-04-29 : 11:26:10

Thanks visakh16. I'm trying this out now.

Edit* That worked!!! Thanks!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-29 : 13:26:16

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources