Post reply

Update with an aggregate

  • December 8, 2009 at 11:31 am

    #214566

    Hello,

    It has been quite awhile since I have tried this and I am having all sorts of problem.

    We have a table that houses the daily sales by branch for each work month of our fiscal year. We have added a new column to the table to hold what percentage of the month's sales each days sales per branch is.

    Here is a glimpse of the data in the table

    Branch Date Sales DailyPercent WorkMonth

    12009-11-30 83176.74 01

    12009-12-01 72503.82 0 1

    12009-12-02 49825.93501

    12009-12-03 39441.78 01

    The basic equation is Sales divided by Sales for the WorkMonth for each branch.

    Do I need a cursor or loop to accomplish this with an update statement?

    Marty

    thanks in advance, any advice or ideas is greatly appreciated!

  • December 8, 2009 at 12:22 pm

    #1089420

    You don't need to use a cursor or a loop. You can update the DailyPercent col for a particular month like this:

    update DailySalesTable

    set DailyPercent = Sales/(

    select sum(Sales)

    from DailySalesTable

    where Workmonth = 1)

    But it would probably be more efficient to just calculate the monthly sales percentage on demand rather than have a special column for it that you have to keep updating, if you can do without it.

    _________________________________
    seth delconte
    http://sqlkeys.com

  • December 8, 2009 at 12:25 pm

    #1089422

    Are you updating all rows?

    The short answer is you probably do not need a cursor, but you might need a subquery to calculate the totals.

    I would try to write the formula you need as a SELECT. Something like

    SELECT

    branch

    , sum( sales) 'TotalSales'

    , WorkMonth

    from MyTable

    group by branch

    , WorkMonth

    to get the sales. Then join this back in to the main query. You could use a CTE (see today's article if you don't know how to write one) for the totals and join that back.

    Select

    branch

    , m.sales

    , m.workmonth

    , m.dailysales

    , (m.dailysales / t.totalsales) * 100 'dailypercent'

    from MyTable m

    inner join TotalSalesCTE t

    on m/branch = t.branch

    and m.workmonth = t.workmonth

    Make sense?

  • December 8, 2009 at 12:48 pm

    #1089447

    Thanks Steve, that should work brilliantly!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply