如果小于或等于10，如何检索总库存 [英] How do I retrieve the total stocks if it is less than or equal to 10

问题描述

嘿伙计们。这些代码有什么问题？如果股票小于或等于10，我试图检索总股票。但我得到了这个错误：

汇总可能除非它出现在HAVING子句或选择列表中包含的子查询中，否则不会出现在WHERE子句中，并且要聚合的列是外部引用。

我尝试过：

 SELECT tp.product_barcode，tp.product_name，tp。 product_supplier，tp.product_type，SUM（ts.stocks_qty）FROM tblproducts AS tp& _ 
INNER JOIN tblstocks AS ts ON ts.product_barcode = tp.product_barcode WHERE SUM（ts.stocks_qty）< 10 GROUP BY tp.product_barcode，& _ 
tp.product_name，tp.product_supplier，tp.product_type; 

解决方案

< blockquote>请参见： SQL GROUP By并且列'名称'在选择列表中无效，因为......错误 [ ^ ]

  SELECT  tp.product_barcode，tp.product_name，tp.product_supplier，tp.product_type，stocks_qty_total 
  FROM  tblproducts  AS  tp 
  INNER  加入（
 选择 product_barcode，SUM（stocks_qty） AS  stocks_qty_total 
 来自 tblStocks 
 组  by  product_barcode） AS  ts  ON  ts.product_barcode = tp.product_barcode 
  WHERE  stocks_qty_total<  10  
  GROUP   BY  tp .product_barcode，tp.product_name，tp.product_supplier，tp.product_type; 

另一种选择：

  SELECT  tp.product_barcode，tp.product_name，tp.product_supplier，tp.product_type，SUM（ts.stocks_qty）
  FROM  tblproducts  AS  tp 
  INNER   JOIN  tblstocks  AS  ts  ON  ts.product_barcode = tp.product_barcode 
  GROUP   BY  tp.product_barcode，tp.product_name，tp.product_supplier，tp.product_type 
  HAVING  SUM（ts.stocks_qty）<  10 ; 

HAVING（Transact-SQL）| Microsoft Docs [ ^ ]

Hey guys. What wrong these code? I am trying to retrieve the total stocks if the stocks is less than or equal to 10. But I got this error:

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:

SELECT tp.product_barcode, tp.product_name, tp.product_supplier, tp.product_type, SUM(ts.stocks_qty) FROM tblproducts AS tp " & _
                "INNER JOIN tblstocks AS ts ON ts.product_barcode = tp.product_barcode WHERE SUM(ts.stocks_qty) < 10 GROUP BY tp.product_barcode, " & _
                "tp.product_name, tp.product_supplier, tp.product_type;

解决方案

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

SELECT tp.product_barcode, tp.product_name, tp.product_supplier, tp.product_type, stocks_qty_total
  FROM tblproducts AS tp 
INNER JOIN (
	Select product_barcode, SUM(stocks_qty) AS stocks_qty_total
	From tblStocks
	Group by product_barcode) AS ts ON ts.product_barcode = tp.product_barcode
WHERE stocks_qty_total < 10
GROUP BY tp.product_barcode, tp.product_name, tp.product_supplier, tp.product_type;

Another alternative:

SELECT tp.product_barcode, tp.product_name, tp.product_supplier, tp.product_type, SUM(ts.stocks_qty) 
FROM tblproducts AS tp
INNER JOIN tblstocks AS ts ON ts.product_barcode = tp.product_barcode 
GROUP BY tp.product_barcode, tp.product_name, tp.product_supplier, tp.product_type
HAVING SUM(ts.stocks_qty) < 10;

HAVING (Transact-SQL) | Microsoft Docs[^]

这篇关于如果小于或等于10，如何检索总库存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！