SQL 聚合函数

发布于 2024-07-12 23:32:52 字数 531 浏览 6 评论 0原文

我想做同样的查询，但不是只是检查数量我想检查“总计”（即数量*价格）。 “价格”是销售表中的一个字段。

这是该链接上建议的原始查询：

SELECT p.[name]
 FROM products p
 WHERE p.product_id in (SELECT s.product_id
     FROM productsales s
     WHERE s.[date] between @dateStart and @dateEnd
     GROUP BY s.product_id
     HAVING Sum(s.quantity) > @X )

因此，我需要将每次销售相加，然后与 @X 进行比较，而不是 Sum(s.quantity) 。（每次销售的价格可能不同）

原文

I have a similar question to this one SQL products/productsales

I want to do that same query but instead of just checking quantity i want to check "total" (ie. quantity * price). "price" is a field in the sales table.

here is the original query suggested on that link:

SELECT p.[name]
 FROM products p
 WHERE p.product_id in (SELECT s.product_id
     FROM productsales s
     WHERE s.[date] between @dateStart and @dateEnd
     GROUP BY s.product_id
     HAVING Sum(s.quantity) > @X )

so instead of Sum(s.quantity) i need to have (s.quantity*s.price) for EACH SALE to be added up and then compared to @X. (the price can be different for each sale)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

乙白 2024-07-19 23:32:52

HAVING (Sum(s.quantity*s.price)) > @X

可能会成功吗？

HAVING (Sum(s.quantity*s.price)) > @X

might do the trick?

回复收藏 0 原文

酒浓于脸红 2024-07-19 23:32:52

（Cagcowboy 的答案被标记为已接受，但评论似乎表明它不起作用，所以这是另一种方法）

此代码使用派生表首先计算出每个产品的“总计”，然后进行分组等是在其之上分层的。

SELECT p.name from products p
WHERE p.product_id IN
    (SELECT product_id from 
        (SELECT product_id, (quantity * price) as total
         FROM productsales WHERE date between @dateStart and @dateEnd) as s
    GROUP by s.product_id
    HAVING sum(total) > @x)

(The answer from Cagcowboy was marked as accepted, but the comment seems to indicate it didn't work, so this is another approach)

This code uses a derived table to first work out the "totals" for each product, then the grouping etc. is layered over the top of that.

SELECT p.name from products p
WHERE p.product_id IN
    (SELECT product_id from 
        (SELECT product_id, (quantity * price) as total
         FROM productsales WHERE date between @dateStart and @dateEnd) as s
    GROUP by s.product_id
    HAVING sum(total) > @x)

回复收藏 0 原文

~没有更多了~