列'medicine.emp_code_number'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。 [英] Column 'medicine.emp_code_number' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

问题描述

我的查询是



选择a.emp_code_number，SUM（a.amt_c）为a，SUM（a.amt_p）为b，SUM（a.amt_t） ）作为c，SUM（a.amt_c）作为d从医学作为内部联接细节作为b在a.c_id = b.c_id顺序上由a.emp_code_number

My query is

select a.emp_code_number, SUM(a.amt_c)as a, SUM(a.amt_p)as b, SUM(a.amt_t) as c, SUM(a.amt_c)as d from medicine as a inner join details as b on a.c_id = b.c_id order by a.emp_code_number

table a 



CREATE TABLE [dbo].[medicine](
	[s_no] [float] NULL,
	[First_Name] [nvarchar](255) NULL,
	[Second_Name] [nvarchar](255) NULL,
	[bill_number] [float] NULL,
	[dt] [nvarchar](255) NULL,
	[shop_name] [nvarchar](255) NULL,
	[Ammount] [float] NULL,
	[medicine_bills] [nvarchar](255) NULL,
	[test_bills] [nvarchar](255) NULL,
	[pres] [nvarchar](255) NULL,
	[columnsdocters_name] [nvarchar](255) NULL,
	[hospital_name] [nvarchar](255) NULL,
	[p_amt] [nvarchar](255) NULL,
	[test_name] [nvarchar](255) NULL,
	[lab_name] [nvarchar](255) NULL,
	[Remarks] [nvarchar](255) NULL,
	[approval_date] [nvarchar](255) NULL,
	[approved] [nvarchar](255) NULL,
	[Rejected] [nvarchar](255) NULL,
	[ty_of_claim] [nvarchar](255) NULL,
	[emp_code_number] [nvarchar](50) NULL,
	[amt_p] [float] NULL,
	[amt_m] [float] NULL,
	[amt_c] [float] NULL,
	[amt_t] [float] NULL,
	[c_bill] [nvarchar](255) NULL,
	[to_amt] [float] NULL,
	[cghs_benid_number] [nvarchar](50) NULL,
	[ward] [varchar](88) NULL,
	[c_id] [varchar](100) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

我的桌子b

my table b

CREATE TABLE [dbo].[medicine](
	[s_no] [float] NULL,
	[First_Name] [nvarchar](255) NULL,
	[Second_Name] [nvarchar](255) NULL,
	[bill_number] [float] NULL,
	[dt] [nvarchar](255) NULL,
	[shop_name] [nvarchar](255) NULL,
	[Ammount] [float] NULL,
	[medicine_bills] [nvarchar](255) NULL,
	[test_bills] [nvarchar](255) NULL,
	[pres] [nvarchar](255) NULL,
	[columnsdocters_name] [nvarchar](255) NULL,
	[hospital_name] [nvarchar](255) NULL,
	[p_amt] [nvarchar](255) NULL,
	[test_name] [nvarchar](255) NULL,
	[lab_name] [nvarchar](255) NULL,
	[Remarks] [nvarchar](255) NULL,
	[approval_date] [nvarchar](255) NULL,
	[approved] [nvarchar](255) NULL,
	[Rejected] [nvarchar](255) NULL,
	[ty_of_claim] [nvarchar](255) NULL,
	[emp_code_number] [nvarchar](50) NULL,
	[amt_p] [float] NULL,
	[amt_m] [float] NULL,
	[amt_c] [float] NULL,
	[amt_t] [float] NULL,
	[c_bill] [nvarchar](255) NULL,
	[to_amt] [float] NULL,
	[cghs_benid_number] [nvarchar](50) NULL,
	[ward] [varchar](88) NULL,
	[c_id] [varchar](100) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

推荐答案

为什么你试着用同名的两个东西打电话？

试试：

Why are you trying to call two things the same name?
Try:

SELECT m.emp_code_number, 
       SUM(m.amt_c) AS a, 
       SUM(m.amt_p) AS b, 
       SUM(m.amt_t) AS c, 
       SUM(m.amt_c) AS d 
FROM medicine m
INNER JOIN details dt 
ON m.c_id = dt.c_id 
ORDER BY m.emp_code_number

使用聚合时，在select statemnet中使用的所有其他未执行聚合的列必须在group by子句中。



根据你的表结构，我认为一列应 amt_m

While using aggregation, all other columns not performing aggregation used in select statemnet must be in group by clause.

As per you table structure, I think one column should be amt_m

select a.emp_code_number
		, SUM(a.amt_c)as a
		, SUM(a.amt_p)as b
		, SUM(a.amt_t) as c
		, SUM(a.amt_m)as d 
from medicine as a 
inner join details as b on a.c_id = b.c_id 
group by a.emp_code_number
order by a.emp_code_number

这篇关于列'medicine.emp_code_number'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！