如何编写查询以从两个表中获取详细信息？ [英] How to write a query to get details from two tables?

问题描述

我有两个名为CustomerDetails和AmountMaster的表。现在我想从CustomerDetails表中获取CustomerName，TotalAmount，我希望从AmountMaster表中获得PaidAmount总数。



我的查询是像这样：

I have two tables which are called "CustomerDetails" and "AmountMaster". Now I want to get "CustomerName, TotalAmount" from "CustomerDetails" table and I want to get total of "PaidAmount" from "AmountMaster" table.

My Query is like this :

select a.CustomerName, a.TotalAmount, SUM(b.PaidAmount )
from CustomerDetails a join AmountMaster b on a.CustomerId=b.CustomerId where b.AmountTypeId=1 and b.PaymentToId=2
order by b.CustomerId

但在这里我得到的错误是这样的



消息8120，等级16 ，状态1，第1行

列'CustomerDetails.CustomerName'在选择列表中无效，因为它不包含在聚合函数或GROUP BY中条款。



这里我希望得到总数为PaidAmount的单曲。那么有人可以帮助我吗？

推荐答案

使用sum函数在查询中你必须使用Group by



正确的查询正在下面....

for use of sum Function in query you have to use Group by

correct Query is belowing....

select a.CustomerName,  SUM(a.TotalAmount) as TotalAmount, SUM(b.PaidAmount ) as PaidAmount 
from CustomerDetails a join AmountMaster b on a.CustomerId=b.CustomerId where b.AmountTypeId=1 and b.PaymentToId=2 group by a.CustomerName
order by b.CustomerId

you你应该使用SUM（）函数d使用分组条款同意选择列表中给出的列

you have use SUM() function so, you should agreegate columns given in select list using Group by clause

select a.CustomerName, a.TotalAmount, SUM(b.PaidAmount )
from CustomerDetails a join AmountMaster b on a.CustomerId=b.CustomerId 
where b.AmountTypeId=1 and b.PaymentToId=2
Group by a.CustomerName, a.TotalAmount

快乐编码！

：）

Happy Coding!
:)

更新您的查询，如下所述：

Update your query as mentioned below :

select a.CustomerName, a.TotalAmount, SUM(b.PaidAmount )
from CustomerDetails a join AmountMaster b on a.CustomerId=b.CustomerId where b.AmountTypeId=1 and b.PaymentToId=2 group by a.CustomerName, a.TotalAmount, b.PaidAmount 
order by b.CustomerId

这篇关于如何编写查询以从两个表中获取详细信息？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！