How i calculate percentage of total in sql 2008

Question

1.00/5 (1 vote)

See more:

hello

how i calculate percentage of total in sql 2008 in this query

SQL

(SELECT SUM(ISNULL(ClientExtractALLInfoReportView.[TotalContractorValue],0)) AS Expr7 FROM ClientExtractALLInfoReportView WHERE (ClientExtractALLInfoReportView.[OPID] = MAX(SS.[ID]))) / (SELECT SUM(ISNULL(ClientExtractALLInfoReportView.[TotalContractorValue],0)) AS Expr7 FROM ClientExtractALLInfoReportView WHERE (ClientExtractALLInfoReportView.[OPID] = MAX(SS.[ID]))) + (SELECT SUM(ISNULL(VInvoices.[EndTotal],0)) AS Expr3 FROM VInvoices WHERE (VInvoices.[OPID] = MAX(SS.[ID]))) + (SELECT SUM(ISNULL(AzenSarfKhazna.[KhaznaTotal],0)) AS Expr4 FROM AzenSarfKhazna WHERE AzenSarfKhazna.[OPID] = MAX(SS.[ID]) AND AzenSarfKhazna.[Type] = 'Other' AND AzenSarfKhazna.[TypeID] IN (SELECT AccountsDefinition.[ID] FROM AccountsDefinition,Directexpenses WHERE AccountsDefinition.[HaveFather] = Directexpenses.[DEXID])) * 100.0 AS Prec

What I have tried:

please help me with this query if you can please

Posted 9-Mar-16 6:02am

Mostafa Salama S

Updated 9-Mar-16 9:22am

Add a Solution

Comments

CHill60 9-Mar-16 12:30pm

What is wrong with your queries?

Mostafa Salama S 9-Mar-16 12:45pm

dose not work right

Maciej Los 9-Mar-16 14:12pm

What exactly "does not work"?
BTW: Too many subqueries!

2 solutions

Solution 2

The logic is:

SQL

SELECT C.GrouppingValue, C.CurrentValue / B.TotalValue * 100 AS Percentage
FROM (
    SELECT A.GrouppingValue, SUM(A.CurrentValue) AS TotalValue
    FROM SomeTable AS A
    GROUP BY A.GrouppingValue
) AS B INNER JOIN SomeTable AS C ON B.GrouppingValue = C.GrouppingValue

Imagine you want to calculate daily sale. Example query might look like:

SQL

SELECT C.SaleDate, C.ProductName, C.Price / B.TotalValue * 100 AS Percentage
FROM (
    SELECT A.SaleDate, A.ProductName, SUM(A.Price) AS TotalValue
    FROM SomeTable AS A
    GROUP BY A.SaleDate, A.PRoductName
) AS B INNER JOIN SomeTable AS C ON B.SaleDate =  C.SaleDate

Feel free to change it to your needs.

Posted 9-Mar-16 9:22am

Maciej Los

Updated 9-Mar-16 9:23am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-03-09T09:10:00

As Maciej Los said - you have too many subqueries.

The thing is almost impossible to read. Use some whitespace (linefeeds) to format it so you can read it.

If you do that you will realise that you are calculating Expr7 twice (the first two sections of your query)

Your query boils down to

Expr7 / Expr7 + Expr3 + Expr4 * 100.0

That is not how to calculate a percentage! Note that Expr7 / Expr7 equals 1!
You also need to use brackets properly as that is actually calculated as

1 + Expr3 + (Expr4 * 100.0)

I have no idea where SS.[ID] is being sourced from.

Other things to note - if you are only selecting from a single table, or if the column name is unique across joined tables, then you don't need to use the table name as a qualifier i.e. use EndTotal instead of VInvoices.EndTotal.
You can give tables ALIASES e.g.

SQL

FROM AccountsDefinition AD
INNER JOIN Directexpenses DE

Both of these points will make your code easier to read

I suggest you throw this query away and start again.

To express A as a percentage of B the formula is

(A / B) * 100.0

Use some variables e.g.

SQL

DECLARE @Expr7 numeric (15,2)
DECLARE @Expr3 numeric (15,2)
DECLARE @Expr4 numeric (15,2)
DECLARE @SSID int = (SELECT MAX([ID]) FROM somewhere)

Then calculate each part separately for now.

I'm guessing you want something like this

SQL

SET @Expr7 = (SELECT SUM(ISNULL([TotalContractorValue],0))
	FROM ClientExtractALLInfoReportView 
	WHERE [OPID] = @SSID)

SET @Expr3 = (SELECT SUM(ISNULL([EndTotal],0))
		FROM VInvoices 
		WHERE [OPID] = @SSID)

SET @Expr4 = (SELECT SUM(ISNULL(AZ.[KhaznaTotal],0))
		FROM AzenSarfKhazna AZ
		WHERE AZ.[OPID] = @SSID
		AND AZ.[Type] = 'Other' 
		AND AZ.[TypeID] IN (
		   SELECT AD.[ID] 
		   FROM AccountsDefinition AD
		   INNER JOIN Directexpenses DE
		   ON AD.[HaveFather] = DE.[DEXID]
	      )

DECLARE @prec numeric(15,2) = 100.0 * @Expr7 / (@Expr3 + @Expr4)

But note - this is just a guess at the final format as you have not given us any information about what data is in what table. I'm also not entirely sure why you have a join to DirectExpenses as you don't appear to be filtering on any columns from it

When you've got that working it might be easier to see where you could use table joins etc to refactor the query. But for now that should at least get you the numbers you want.