带和的样本查询 [英] sample query with sum

问题描述

如何计算2个表中2个值的总和



示例

表1表2 
 Sno Overallqty地址Sno shiqty Airqty地址
  1   50  mumbai  1   20   25 孟买
  2   30  chennai  2   12   10  chennai 
  3   80  delhi  3   50   30  delhi 

在这些例子中，我需要计算totalQTY的总和 - （Shipty + Airqty） ）

输出必须显示整体数量 - （shipqty + ai rqty）= 0并不在我们的查询中显示否则必须显示



示例输出提示

在此示例中，它必须显示前2行因为第三行总数为零（80-50 + 30）= 0

解决方案

试试这个，

 选择 t1。*，t2。* 来自 table1 t1内部 join  table2 t2 on t1.address = t2.address 
  where （t1.Overallqty - （t2.shiqty + t2.Airqty））>  =  0  

考虑totalqty，shiqty，Airqty的数据类型都是int或decimal而且Overallqty - （shiqty + Airqty）必须大于0满足您的要求。

根据您的解释，您看起来不需要SUM或任何聚合函数。

尝试类似的事情 -

  SELECT  T1.Sno，T1.Overallqty ，T1.Address，T2.Sno，T2.shiqty，T2.Airqty，T2.Address 
  FROM 表1 T1 
  INNER   JOIN 表2 T2  ON  T1.Sno = T2.Sno 
  WHERE  T1.Overallqty  - （T2.shiqty + T2.Airqty）> 0 

希望，它会有所帮助。

如果您的要求比这更重要，请告诉我。 ：）

Hi,
How to calculate sum of 2 values in 2 tables

Example

Table1				            Table2			
Sno	Overallqty	Address		Sno	shiqty	 Airqty	  Address
1	50	       mumbai		1	20	     25	   mumbai
2	30	       chennai		2	12	     10	   chennai
3	80	       delhi		3	50	     30	   delhi

In these example i need to calculate Sum of overallQTY-(Shipty+Airqty)
In output has to show if the Overallqty-(shipqty+airqty)=0 doesnt show in our query else it has to display

Sample output hint
In this example it has to show first 2 lines because third line will be zero overall qty(80-50+30)=0

解决方案

Try this,

select t1.*,t2.* from table1 t1 inner join table2 t2 on t1.address=t2.address
where (t1.Overallqty - (t2.shiqty + t2.Airqty))>=0	 

Consider that datatype of Overallqty , shiqty, Airqty all are int or decimal and Overallqty - (shiqty + Airqty) must be greater than 0 which satisfied your requirement.

From what you explained it doesn't look like you need SUM or any aggregate functions.
Try something like-

SELECT T1.Sno, T1.Overallqty, T1.Address, T2.Sno, T2.shiqty, T2.Airqty, T2.Address
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Sno=T2.Sno
WHERE T1.Overallqty - (T2.shiqty + T2.Airqty)>0

Hope, it helps.
Please let me know if your requirement is somethingelse than this. :)

这篇关于带和的样本查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！