如何计算数据来自其他表的I列？ [英] How to compute I column that data is from other table?

问题描述

我有一个2表tblPayment有一列'Payment';并且tblMonthlyReports的列为'Gross'。

我试图将tblPayment中的所有付款列在Payment中，结果将显示在tblMonthlyReports的Gross列中。但我得到一个错误说

I have a 2 tables the tblPayment that have a column of 'Payment'; and the tblMonthlyReports having a column of 'Gross'.
I am trying to sum all the payments in column Payment in tblPayment the result will be display in Gross column in tblMonthlyReports. But I got an error saying

Msg 147, Level 15, State 1, Line 12
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

我是什么尝试过：

What I have tried:

USE [NEWCMO]
GO

SELECT [Months]
      ,[Patient/Month]
      ,[Gross]
      ,[Abatement]
      ,[Net]
  FROM [dbo].[tblMonthlyReports]
GO

SELECT Payment FROM tblPayment WHERE Payment=(SELECT SUM(Payment) AS Gross FROM tblMonthlyReports);

推荐答案

如果您正在使用聚合函数，则必须添加 GROUP BY 子句告诉SQL引擎应该通过哪个列值聚合（分组）数据。 HAVING 子句可用于过滤生成的行，但如果不需要，则不需要这样做。您的查询应如下所示：

If you're using aggregate functions you always have to add a GROUP BY clause to tell the SQL engine by which column value it should aggregate (group) the data. The HAVING clause may be used to filter the resulting rows but this is not required, if not needed. Your query should look something like:

SELECT Months
     , SUM(Payment) AS Gross
  FROM tblMonthlyReports
GROUP BY Months
WHERE Months IS NOT NULL
  AND Payment IS NOT NULL

这将导致几个记录，每个记录显示所属月份的付款总额（一个记录为每个月发生在tblMonthlyReports中。



...并且不要忘记 WHERE 子句，否则聚合可以在第一个NULL值w处停止会导致错误的结果！

THis will result in several records each showing the sum of payments for the belonging month (one record for each month that occurs in tblMonthlyReports).

...and don't forget the WHERE clause, otherwise the aggreation may stop at the first NULL value which will lead into incorrect results!

这篇关于如何计算数据来自其他表的I列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！