如何获得每种产品的stocks_qty总和 [英] How do I get the sum of stocks_qty of each product

问题描述

嘿。我只是不知道如何解决这个错误。我想得到stocks_qty的总和。



表：

 CREATE TABLE tblproducts（
 product_barcode varchar （50）PRIMARY KEY NOT NULL，
 product_name varchar（50）NOT NULL，
 product_sprice int NOT NULL，
 product_type varchar（50）NOT NULL，
 product_supplier varchar（50） NOT NULL，
 product_unit varchar（50）NOT NULL 
）; 
 
 CREATE TABLE tblstocks（
 stocks_id int IDENTITY（1,1）PRIMARY KEY NOT NULL，
 stocks_qty int NOT NULL，
 stocks_status varchar（50）NOT NULL， 
 stocks_date varchar（100）NOT NULL，
 stocks_expiration varchar（100）NOT NULL，
 product_barcode varchar（50）NOT NULL FOREIGN KEY REFERENCES tblproducts（product_barcode）
）; 

但是我收到了这个错误：

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

我尝试过：

 SELECT tp。*，SUM（ts.stocks_qty）AS stocks FROM tblproducts AS tp LEFT JOIN tblstocks AS ts ON ts.product_barcode = tp.product_barcode GROUP BY ts.product_barcode; 

解决方案

看看这个： [ ^ ]

好吧，错误信息是自我解释！您必须添加SELECT语句中使用的 tblproducts 表中的所有字段，或者您必须提供您希望显示的字段数。这将解决您的问题：

  SELECT  tp.product_barcode，SUM（ts.stocks_qty） AS 股票
  FROM  tblproducts  AS  tp < span class =code-keyword> LEFT   JOIN  tblstocks  AS  ts  ON  ts.product_barcode = tp.product_barcode 
  GROUP   BY  ts.product_barcode; 

另一种方法是使用SUM函数和 OVER子句 [ ^ ]，请参阅：具有OVER子句的SQL Server聚合函数| PTR [ ^ ]

Hey. I just don't know on how to fix this error. I want to get the sum of stocks_qty.

Table:

CREATE TABLE tblproducts (
	product_barcode varchar(50) PRIMARY KEY NOT NULL,
	product_name varchar(50) NOT NULL,
	product_sprice int NOT NULL,
	product_type varchar(50) NOT NULL,
	product_supplier varchar(50) NOT NULL,
	product_unit varchar (50) NOT NULL
);

CREATE TABLE tblstocks (
	stocks_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	stocks_qty int NOT NULL,
	stocks_status varchar(50) NOT NULL,
	stocks_date varchar(100) NOT NULL,
	stocks_expiration varchar(100) NOT NULL,
	product_barcode varchar(50) NOT NULL FOREIGN KEY REFERENCES tblproducts(product_barcode)
);

But I got this error:

Column 'tblproducts.product_barcode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What I have tried:

SELECT tp.*, SUM(ts.stocks_qty) AS Stocks FROM tblproducts AS tp LEFT JOIN tblstocks AS ts ON ts.product_barcode = tp.product_barcode GROUP BY ts.product_barcode;

解决方案

Check this out: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]

Well, an error message is self explanatory! You have to add all fields from tblproducts table which are used in SELECT statement or you have to provide as many fields name as many you wish to display. This will resolve your issue:

SELECT tp.product_barcode, SUM(ts.stocks_qty) AS Stocks
FROM tblproducts AS tp LEFT JOIN tblstocks AS ts ON ts.product_barcode = tp.product_barcode
GROUP BY ts.product_barcode;

Another way is to use SUM function with OVER clause[^], see: SQL Server Aggregate Functions with OVER Clause | PTR[^]

这篇关于如何获得每种产品的stocks_qty总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！