Adding Calculated Fields

[Scrappy]

I have a table with some fields. break_mon, lunch_mon, dinner_mon, ent_mon,
break_tue, lunch_tue, dinner_tue, ent_tue, .....etc

I want to output the sum of the four monday columns as well as the sum of
the four tuesday columns.

I did this with

SELECT break_mon+lunch_mon+dinner_mon+ent_mon AS mon_tot,
break_tue+lunch_tue+dinner_tue+ent_tue AS tue_tot
FROM expense_report

I now want to add mon_tot and tue_tot AS total, but SQL is telling me that
it can not find mon_tot. Is there a way to do this? Thanks!

Darren
MCP

[John Gilson]

"Scrappy" <ce*****@lan-specialist.com> wrote in message
news:YW********************@twister.nyroc.rr.com.. .

I have a table with some fields. break_mon, lunch_mon, dinner_mon, ent_mon,
break_tue, lunch_tue, dinner_tue, ent_tue, .....etc

I want to output the sum of the four monday columns as well as the sum of
the four tuesday columns.

I did this with

SELECT break_mon+lunch_mon+dinner_mon+ent_mon AS mon_tot,
break_tue+lunch_tue+dinner_tue+ent_tue AS tue_tot
FROM expense_report

I now want to add mon_tot and tue_tot AS total, but SQL is telling me that
it can not find mon_tot. Is there a way to do this? Thanks!

Darren
MCP

SELECT mon_tot, tue_tot, mon_tot + tue_tot AS total
FROM (SELECT break_mon+lunch_mon+dinner_mon+ent_mon AS mon_tot,
break_tue+lunch_tue+dinner_tue+ent_tue AS tue_tot
FROM expense_report) AS T

Regards,
jag

[louis nguyen]

> SELECT break_mon+lunch_mon+dinner_mon+ent_mon AS mon_tot,

break_tue+lunch_tue+dinner_tue+ent_tue AS tue_tot
FROM expense_report

I now want to add mon_tot and tue_tot AS total, but SQL is telling me that
it can not find mon_tot. Is there a way to do this? Thanks!

Hi Darren,

Make your query into a derived table. (Wrap with parentheses and give
it an alias). The expense_report table really should be normalized.
In the long run, it will make your code cleaner and more maintainable.
- Louis

select mon_tot+tue_tot as total
from
(
SELECT break_mon+lunch_mon+dinner_mon+ent_mon AS mon_tot,
break_tue+lunch_tue+dinner_tue+ent_tue AS tue_tot
FROM expense_report
) as a

[Scrappy]

Thanks! I had one question! I noticed in Enterprise manager when in Design
mode, you can create a field and then add a formula right to the field. Is
this way better or worse as far as speed goes then using a query? Thanks!
"louis nguyen" <lo************@hotmail.com> wrote in message
news:b0*************************@posting.google.co m...

SELECT break_mon+lunch_mon+dinner_mon+ent_mon AS mon_tot,
break_tue+lunch_tue+dinner_tue+ent_tue AS tue_tot
FROM expense_report

I now want to add mon_tot and tue_tot AS total, but SQL is telling me that it can not find mon_tot. Is there a way to do this? Thanks!

Hi Darren,

Make your query into a derived table. (Wrap with parentheses and give
it an alias). The expense_report table really should be normalized.
In the long run, it will make your code cleaner and more maintainable.
- Louis

select mon_tot+tue_tot as total
from
(
SELECT break_mon+lunch_mon+dinner_mon+ent_mon AS mon_tot,
break_tue+lunch_tue+dinner_tue+ent_tue AS tue_tot
FROM expense_report
) as a

[louis nguyen]

> Thanks! I had one question! I noticed in Enterprise manager when in Design

mode, you can create a field and then add a formula right to the field. Is
this way better or worse as far as speed goes then using a query? Thanks!

Hi,

I think you're talking about computed columns. It depends. If all
that is required is the simple calcs, in the first post, computed cols
are fine. However, if your requirements change and you find yourself
altering code constantly, I find stored procedures the way to go. In
the long run it saves my sanity, but that's me. -- Louis