Update Column with % of total for each row.

Ziggadebo · #1 (**permalink**) 12-09-11, 06:30

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.

r937 · #2 (**permalink**) 12-09-11, 06:36

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

Ziggadebo · #3 (**permalink**) 12-09-11, 07:45

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)

Quote:

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?

r937 · #4 (**permalink**) 12-09-11, 08:27

Quote:

Originally Posted by Ziggadebo

Brilliant, thats exactly what I was after.

actually, it seems you were after an update

Quote:

Originally Posted by Ziggadebo

Any ideas?

sure

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

Ziggadebo · #5 (**permalink**) 12-09-11, 08:42

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech