我如何计算sql 2008中的总百分比 [英] How i calculate percentage of total in sql 2008

问题描述

你好



如何在这个查询中计算sql 2008中的总百分比

（< span class =code-keyword> SELECT  SUM（ISNULL（ClientExtractALLInfoReportView。[TotalContractorValue]， 0 ）） AS  Expr7  FROM  ClientExtractALLInfoReportView  WHERE （ClientExtractALLInfoReportView。[OPID] = MAX（SS。[ ID]）））/（ SELECT  SUM（ISNULL（ClientExtractALLInfoReportView。[TotalContractorValue]， 0 ））< span class =code-keyword> 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。[类型] = ' 其他'  AND  AzenSarfKhazna。[TypeID]  IN （< span class =code-keyword> SELECT  AccountsDefinition。[ID]  FROM  AccountsDefinition，Directexpenses  WHERE  AccountsDefinition。[HaveFather] = Directexpenses。[DEXID]））*  100 。 0   AS  Prec 

我的尝试：



如果可以，请帮我解决这个问题

解决方案

正如Maciej Los所说 - 你有太多的子查询。 />


这件事几乎不可能阅读。使用一些空格（换行）来格式化它以便你可以阅读它。



如果你这样做，你会发现你正在计算 Expr7 两次（查询的前两个部分）



您的查询归结为

 Expr7 / Expr7 + Expr3 + Expr4 * 100.0 

这不是如何计算百分比！请注意 Expr7 / Expr7 等于1！

您还需要正确使用括号，因为实际计算为

 1 + Expr3 +（Expr4 * 100.0）

我不知道在哪里 SS。[ID] 来自。



其他需要注意的事项 - 如果您只是从一个表中选择，或者列名在连接时是唯一的表，那么你不需要使用表名作为限定符，即使用 EndTotal 而不是 VInvoices.EndTotal 。

您可以给表格ALIASES例如

  FROM  AccountsDefinition AD 
  INNER   JOIN  Directexpenses DE 

这些点将使您的代码更容易阅读



我建议您抛弃此查询并重新开始。



要表示A占B的百分比，公式为

（A / B）* 100.0 

使用一些变量，例如

  DECLARE   @Expr7  数字（ 15 ， 2 ） 
  DECLARE   @Expr3  数字 （ 15 ， 2 ）
  DECLARE   @Expr4   numeric （ 15 ， 2 ）
  DECLARE   @SSID   int  =（ SELECT  MAX（[ID]） FROM 某处）

然后暂时单独计算每个部分。



我猜你想要这样的东西

  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 ] = ' 其他' 
  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 ）

但请注意 - 这只是对最终格式的猜测，因为您没有向我们提供有关哪些数据在哪个表中的任何信息。我也不完全确定为什么你加入DirectExpenses，因为你似乎没有过滤任何列上的



当你得到它时工作它可能更容易看到你可以使用表连接等来重构查询。但是现在至少应该得到你想要的数字。

逻辑是：

  SELECT  C.GrouppingValue，C.CurrentValue / B.TotalValue *  100   AS 百分比
  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 

想象一下你想要c每日销售。示例查询可能如下所示：

  SELECT  C.SaleDate，C.ProductName，C.Price / B .TotalValue *  100   AS 百分比
  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 

随意将其更改为您的需求。

hello

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

(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

解决方案

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.

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.

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

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.

The logic is:

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:

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.

这篇关于我如何计算sql 2008中的总百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！