Adding Calculated Fields

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Scrappy
    #1

    Adding Calculated Fields

    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


    Tags: None
    • John Gilson
      #2
      Re: Adding Calculated Fields

      "Scrappy" <celtics@lan-specialist.com> wrote in message
      news:YW8vb.1177 84$ji3.70406@tw ister.nyroc.rr. com...[color=blue]
      > 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[/color]

      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


        Comment

        • louis nguyen
          #3
          Re: Adding Calculated Fields

          > SELECT break_mon+lunch _mon+dinner_mon +ent_mon AS mon_tot,[color=blue]
          > 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![/color]

          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

            Comment

            • Scrappy
              #4
              Re: Adding Calculated Fields

              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" <louisducnguyen @hotmail.com> wrote in message
              news:b0e9d53.03 11201455.50b7e4 8f@posting.goog le.com...[color=blue][color=green]
              > > 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[/color][/color]
              that[color=blue][color=green]
              > > it can not find mon_tot. Is there a way to do this? Thanks![/color]
              >
              > 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[/color]


                Comment

                • louis nguyen
                  #5
                  Re: Adding Calculated Fields

                  > Thanks! I had one question! I noticed in Enterprise manager when in Design[color=blue]
                  > 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![/color]
                  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

                    Comment

                    Previous template Next
                    Working...