Calculating the sum of a calculated 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 the sum of a calculated column

Author

Topic

sarorelasoul
Starting Member

29 Posts

Posted - 2008-03-28 : 12:09:48

Hi,

I'm writing a query that calculates values for each id, then I need to sum up all the values for each id and put them in another field, here is what I wrote but SQL cannot understand the column that I calculeted

select id,term_cd,
case when RIGHT(term_cd, 1) IN ('6') and substring(term_cd, 2,4) <= 2004) then '1'
when substring(term_cd, 2,4) <= 2004 and RIGHT(term_cd, 1) = 5 then '0.5'
else '1' end as term_count,
SUM(term_count) AS ttd_enrolled
group by id,term_cd

SQL gives me an error "invalid column name tern_count" in the line where I calculate the sum.
what's wrong with the query? or should I calculate the term_count in an inner query?

Appretiate your help

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-28 : 12:18:58

[code]select id,term_cd,
sum(case when RIGHT(term_cd, 1) IN ('6') and substring(term_cd, 2,4) <= 2004 then 1.0
when substring(term_cd, 2,4) <= 2004 and RIGHT(term_cd, 1) = 5 then 0.5
else 1.0 end
) AS ttd_enrolled
from tbl
group by id,term_cd[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 12:19:29

[code]SELECT id,
term_cd,
SUM(
CASE
WHEN RIGHT(term_cd, 1) = '5' AND SUBSTRING(term_cd, 2, 4) <= '2004' THEN 0.5
ELSE 1.0
END
) AS ttd_enrolled
GROUP BY id,
term_cd[/code]

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-28 : 12:35:04

You need a FROM clause in your SELECT statement.

CODO ERGO SUM

sarorelasoul
Starting Member

29 Posts

Posted - 2008-03-28 : 12:35:45

thanks a lot
I no more have this error, but the problem is that the grouping is not woring.
Any clue?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-28 : 12:38:42

What do you mean by not working? Post sample data and expected output.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sarorelasoul
Starting Member

29 Posts

Posted - 2008-03-28 : 12:50:21

Here is the data before the query
id term_cd
------------
1 120046
1 120036
1 120025
2 119996
2 120015
3 120026

Expecter result
id term_cd ttd_enrolled
-------------------------
1 2.5
2 1.5
3 1

Another problem I'm facing is that I don't want the term_cd to be included in the results
Here is what I get
id term_cd ttd_enrolled
-----------------------
1 120046 1
1 120036 1
1 120025 0.5
2 119996 1
2 120015 0.5
3 120026 1

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-28 : 12:57:21

Then remove term_cd from SELECT and GROUP BY clause.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sarorelasoul
Starting Member

29 Posts

Posted - 2008-03-28 : 13:39:49

Thanks a lot for your help, it's working now

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources