# Thread: Update Column with % of total for each row.

1. Registered User
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
02/11/2011 Pete 2
02/11/2011 Paul 4

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
02/11/2011 Pete 2 0.05
02/11/2011 Paul 4 0.10

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?

2. SQL Consultant
Join Date
Apr 2002
Location
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```

3. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Brilliant, thats exactly what I was after.
actually, it seems you were after an update

Any ideas?
sure

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

5. Registered User
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
•