Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Posts
    49

    Unanswered: Update Column with % of total for each row.

    I'm trying to work out the value of a item as a % of the group.

    Using my simplified table below (table called sweetie)

    Date Name Sweets
    01/11/2011 Pete 5
    01/11/2011 Paul 10
    01/11/2011 Paddy 15
    02/11/2011 Pete 2
    02/11/2011 Paul 4
    02/11/2011 Paddy 6

    I would like to select a column called PERC, which would be the sum of Sweets/sum of Sweets(grouped by date)

    So for example for the first value I would have 5/(5+10+15)

    I can write the query so it totals all the sweets, but I want to use totals by date and am unsure how I would do this.

    My query so far is (but this doesn't use totals by date)

    select date, name, sweets, sweets/((select sum(sweets) from sweetie a) as PERC
    from sweetie

    Would return the results

    Date Name Sweets PERC
    01/11/2011 Pete 5 0.12
    01/11/2011 Paul 10 0.24
    01/11/2011 Paddy 15 0.36
    02/11/2011 Pete 2 0.05
    02/11/2011 Paul 4 0.10
    02/11/2011 Paddy 6 0.14

    How can I get it to calculate based on the sum by date. Would I need to declare a variable for each date, that variable being the sum of sweets?

    Thanks for any help you can give.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT date
         , name
         , sweets
         , sweets / d.date_sum AS perc
      FROM sweetie AS t
    INNER
      JOIN ( SELECT date
                  , SUM(sweets) as date_sum
               FROM sweetie
             GROUP
                 BY date ) AS d
        ON d.date = t.date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2010
    Posts
    49
    Brilliant, thats exactly what I was after.

    However when I take it a step further and try and use within an update statement as: I get this error message

    (note i created a column called PERC within the table)

    Msg 512, Level 16, State 1, Line 1
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated.
    Code:
    update sweetie
    set PERC = 
    (SELECT 
         sweets / d.date_sum * 100 AS perc
      FROM sweetie AS t
    INNER
      JOIN ( SELECT date
                  , SUM(sweets) as date_sum
               FROM sweetie
             GROUP
                 BY [date] ) AS d
        ON d.date = t.date)
    Any ideas?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Ziggadebo View Post
    Brilliant, thats exactly what I was after.
    actually, it seems you were after an update

    Quote Originally Posted by Ziggadebo View Post
    Any ideas?
    sure

    abandon the idea of storing the percentage, and the need for you to fix that update statement vanishes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2010
    Posts
    49
    fair enough challenge.

    Originally I only wanted a select statement, as I can simply run it when I need the data.


    Usually in my limited knowledge if I can work out the select statement I can then adapt it to do an update.

    The update would be nice to have as I can set it as a trigger when more data is inserted into the table.

    I guess the moral of the story here is think things through thoroughly at the outset!

    But I do agree your solution would work.



    Thanks anyway you're first bit of assistance was most helpful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •