Calculating Column w/ Another Derived Column

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

Calculating Column w/ Another Derived Column

Author

Topic

DotNetInt
Starting Member

9 Posts

Posted - 2007-06-06 : 19:21:45

Can someone help me with the following query? I need to take the derived value from one column and calculate another column with it.

SELECT UserID,

((SELECT COUNT(*) FROM HPN_LeadMatches)+(SELECT COUNT(*) FROM HPN_Painters)) As Total,

(SELECT COUNT(*) FROM HPN_Leads) / Main.Total

FROM HPN_LeadMatches As Main

The error i'm getting says is unkown column 'total'. Is there another way to accomplish this?

Thanks!!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-06 : 19:29:27

You need to replace the alias with the entire column definition. The main.total has no idea about the computation you are doing in the previous column. So replace the main.total with the entire (SELECT.. + SELECT..).

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

DotNetInt
Starting Member

9 Posts

Posted - 2007-06-07 : 11:34:11

I considered that, but my only concern is that the query will be ran multiple times, as this is actually a much larger query. Here is the basic structure of it...at least what I would like to accoumplish.

Select (Subquery1 + Subquery2 + Subquery3) AS Total,
((AnotherSubquery1) / MainQuery.Total) AS Column2,
((AnotherSubquery2) / MainQuery.Total) AS Column3,
((AnotherSubquery3) / MainQuery.Total) AS Column4,
((AnotherSubquery4) / MainQuery.Total) AS Column5,
((AnotherSubquery5) / MainQuery.Total) AS Column7,
etc...
FROM Table AS MainQuery

If I replace the '/ MainQuery.Total' with:
'/ (Subquery1 + Subquery2 + Subquery3)', will the Total column be repetatively calculated in each of the columns?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-07 : 11:43:10

Another way to do this is to do it in 2 SELECTs. Grt the total into a variable in first query, then use that variable in yor 2nd SELECT to get other columns.You can run profiler and see which method costs more..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

DotNetInt
Starting Member

9 Posts

Posted - 2007-06-07 : 12:10:43

I don't believe this would work, as the 'Total' subquery is based on the main query's ID, so each row will be different

Select ID,
(Subquery1 Where ID = MainQuery.ID +
Subquery2 Where ID = MainQuery.ID +
Subquery3 Where ID = MainQuery.ID) AS Total,
((AnotherSubquery1) / MainQuery.Total) AS Column2,
((AnotherSubquery2) / MainQuery.Total) AS Column3,
((AnotherSubquery3) / MainQuery.Total) AS Column4,
((AnotherSubquery4) / MainQuery.Total) AS Column5,
((AnotherSubquery5) / MainQuery.Total) AS Column7,
etc...
FROM Table AS MainQuery

I suppose I will have to programatically go through with multiple loops.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-07 : 12:40:37

hmm.. perhaps you can get the Total into a table variable along with the ID and use the @table to join the other table with ID to get other columns?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

DotNetInt
Starting Member

9 Posts

Posted - 2007-06-07 : 13:35:50

Good Idea. I can but the Total subquery in a view, and then pull the 'Total' value from it. e.g.

Create View Total_View
As
Select ID,
(Subquery1 Where ID = MainQuery.ID +
Subquery2 Where ID = MainQuery.ID +
Subquery3 Where ID = MainQuery.ID) AS Total,
FROM Table AS MainQuery

Then...

SELECT ID,
(
(AnotherSubquery1) /
(SELECT Total From Total_View Where ID = MainQuery.ID)
) AS Column2,
(
(AnotherSubquery2) /
(SELECT Total From Total_View Where ID = MainQuery.ID)
) AS Column3,
etc...
FROM Table AS MainQuery

This should work perfectly. Thanks for the help dinakar!!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-07 : 13:39:06

Sure..its probably better to verify if this is more efficient via profiler..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources