编写SQL查询以对字段执行某些计算 [英] Write a SQL Query to perform some calculation on fields

问题描述

我有下表：

 Billing_Acct乘数总计
 111 12 200 
 111 12 400 
 123 5 100 
 123 5 300 
 123 5 600 
 241 10 400 

现在我想要另一列其结果执行类似的计算（对于相同的Billing_acct，我想添加Total，然后乘以乘数）



结果会是这样的：

 Billing_Acct Multiplier总结果
 111 12 200 7200 
 111 12 400 7200 
 123 5 100 5000 
 123 5 300 5000 
 123 5 600 5000 
 241 10 400 4000 

如何为此编写Sql查询？

或如何在Excel中执行此任务？



提前致谢。

解决方案

< blockquote>如果我正确理解了这个要求，可以通过多种方式完成，例如：

  SELECT  a.Billing_Acct，
 a.Multiplier，
 a.Total，
 c.multiplier * c.total  as  result 
  FROM  TableName a，
（ SELECT  b.Billing_Acct，
 AVG（b.Multiplier） AS 乘数，
 SUM（b.Total） AS 总计
  FROM  TableName b 
  GROUP   BY  b.Billing_Acct）c 
  WHERE  a.Billing_Acct = c.Billing_Acct; 

I have a following table :

Billing_Acct   Multiplier   Total
111               12          200
111               12          400
123               5           100
123               5           300
123               5           600
241               10          400

Now I want another column whose results perform calculations like (for same Billing_acct I want to add Total and than multiply that by multiplier )

Result would be something like that:

Billing_Acct   Multiplier   Total     Result
111               12          200      7200
111               12          400      7200
123               5           100      5000
123               5           300      5000
123               5           600      5000
241               10          400      4000

How to write a Sql Query for this ?
or How to do this task in Excel ?

Thanks in advance.

解决方案

If I understood the requirement correctly, this can be done in several ways, for example something like:

SELECT a.Billing_Acct,
       a.Multiplier,
       a.Total,
       c.multiplier * c.total as result
FROM TableName a,
     (SELECT b.Billing_Acct,
            AVG(b.Multiplier) AS multiplier,
            SUM(b.Total)      AS Total
     FROM  TableName b
     GROUP BY b.Billing_Acct) c
WHERE a.Billing_Acct = c.Billing_Acct;

这篇关于编写SQL查询以对字段执行某些计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！