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
vbnetcoderAsked:
Who is Participating?
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.

dsackerContract ERP Admin/ConsultantCommented:
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

Open in new window

0
vbnetcoderAuthor 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)
0
dsackerContract ERP Admin/ConsultantCommented:
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.

Assuming your table is already loaded, you can lose the INSERT INTO statements.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

vbnetcoderAuthor 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)
0
dsackerContract ERP Admin/ConsultantCommented:
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 *).
0
vbnetcoderAuthor Commented:
That makes sense


It doesn't like this syntax:

INSERT INTO @MyTable VALUES (SELECT * FROM MyTable)

Incorrect syntax near the keyword 'SELECT'.
0
dsackerContract ERP Admin/ConsultantCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vbnetcoderAuthor Commented:
ty
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.