CODEWITHSUNDEEP
sqlsql-serversql-server-2005
user3532047
user3532047

Reputation: 11

SQL Server 2005 Dividing two rows in a column

I have a table set up like this:

        Description                Jan |  Feb  | Mar  | Apr .....   
Volume        

Days in Month

Gal/Day Average

I would like to be able to divide the volume for each month by the Days (and multiply by 100) to fill in the gal/day average row for each month.

The farthest I've gotten code wise is:

Update Table
set [Jan] = ?
where Description = 'Gal/Day Average'

Upvotes: 0

Views: 160

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

Not a very good data format, but you could do:

Update toupdate
    set [Jan] = volume.jan / dim.jan
    from table toupdate cross join 
         table volume cross join
         table dim
    where toupdate.description = 'Gal/Day Average' and
          volume.description = 'Volume' and
          dim.description = 'Days In Month';

A much better data format would have the columns:

  • month
  • (possibly year)
  • volume
  • daysinmonth
  • average

Upvotes: 1

Related Questions