SQL - percentage of a whole

I have a select statement

Select Name, MarketValue

Which returns dollar amount

I want to know what percent a particular MarketValue is of a whole.

For example if this was the data:

Name AAAA
Market Value 400

Name bbbbb
Market Value 200

Name bbbbb
Market Value 600

The total would be 1200

in the case of aaa i would want to return 0.333333

400 is what percent of 1200
= 400 / 1200
= 0.333333
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Here is a working solution, using a temporary table:

``````DECLARE @MyTable TABLE (
Name        varchar(8)      NOT NULL,
MarketValue decimal(13, 2)  NOT NULL )

INSERT INTO @MyTable VALUES ('AAAA', 400)
INSERT INTO @MyTable VALUES ('BBBBB', 200)
INSERT INTO @MyTable VALUES ('BBBBB', 600)

SELECT  t1.Name,
SUM(t1.MarketValue) AS SumMktValue,
(SUM(t1.MarketValue) * 100) / TotMktValue AS MktPct
FROM    @MyTable t1
JOIN   (SELECT SUM(MarketValue) AS TotMktValue FROM @MyTable) t2
ON      1 = 1
GROUP BY t1.Name, TotMktValue
``````
Author Commented:
That looks like what i need.

Instead of the following lines is there some way i could do a select statement from a table?

INSERT INTO @MyTable VALUES ('AAAA', 400)
INSERT INTO @MyTable VALUES ('BBBBB', 200)
INSERT INTO @MyTable VALUES ('BBBBB', 600)
Certainly. My example is of a temporary table. Simply change the @MyTable to whatever table you desire to use, and change the field names accordingly.

Author Commented:
I think i need the temp table i just need to select into it. Can i do something like this?

INSERT INTO @MyTable VALUES (SELECT * FROM MyTable)
You'll need to define the fields in your @MyTable according to what you want to select.

If you are selecting *, you'll need to define every field.

If you only want to select certain fields, it's less coding to create your @MyTable with just those fields, then explicitly SELECT them (instead of *).
Author Commented:
That makes sense

It doesn't like this syntax:

INSERT INTO @MyTable VALUES (SELECT * FROM MyTable)

Incorrect syntax near the keyword 'SELECT'.
When you INSERT via a SELECT statement, you don't need the VALUES.

It's also more readable to split the lines in that situation, since your SELECT could be a lot longer. :)

INSERT INTO @MyTable
SELECT * FROM MyTable

Experts Exchange Solution brought to you by