Calculate percent based on SUM

I'd like to calculate the percent of the total for column DataValue. So if
the sum of column DataValue is 200 and the values for record A is 25 I'd
like to return 12.5%
Based on my sample data I'd like to return:
A,12.5
B,25
C,12.5
D,50
CREATE TABLE TEST
(ID varchar(1),DataValue int)
INSERT INTO TEST (ID,DataValue)VALUES('A',25)
INSERT INTO TEST (ID,DataValue)VALUES('B',50)
INSERT INTO TEST (ID,DataValue)VALUES('C',25)
INSERT INTO TEST (ID,DataValue)VALUES('D',100)
Thanks...SELECT id, datavalue/
(SELECT CAST(SUM(datavalue) AS REAL)
FROM Test)*100
FROM Test
David Portas
SQL Server MVP
--|||Try,
use northwind
go
CREATE TABLE TEST
(ID varchar(1),DataValue int)
INSERT INTO TEST (ID,DataValue)VALUES('A',25)
INSERT INTO TEST (ID,DataValue)VALUES('B',50)
INSERT INTO TEST (ID,DataValue)VALUES('C',25)
INSERT INTO TEST (ID,DataValue)VALUES('D',100)
go
select
a.[id],
(a.datavalue * 100.00) / nullif(b.sum_datavalue, 0) as col_percent
from
test as a
inner join
(
select sum(datavalue) from test
) as b(sum_datavalue)
on 1 = 1
go
drop table test
go
AMB
"Terri" wrote:

> I'd like to calculate the percent of the total for column DataValue. So if
> the sum of column DataValue is 200 and the values for record A is 25 I'd
> like to return 12.5%
> Based on my sample data I'd like to return:
> A,12.5
> B,25
> C,12.5
> D,50
> CREATE TABLE TEST
> (ID varchar(1),DataValue int)
> INSERT INTO TEST (ID,DataValue)VALUES('A',25)
> INSERT INTO TEST (ID,DataValue)VALUES('B',50)
> INSERT INTO TEST (ID,DataValue)VALUES('C',25)
> INSERT INTO TEST (ID,DataValue)VALUES('D',100)
> Thanks...
>
>