mysql 多重计数并与 WHERE 求和

发布于 2024-11-02 00:45:16 字数 372 浏览 0 评论 0原文

我正在尝试构建一个查询来检查我的库存。

SELECT COUNT(*) AS item_count,
reseller_id, sum(sold) as
sold_count, sum(refunded) as
refunded_count,**sum(case price when
refunded <> 1 AND sold=1) as pricesum** FROM stock
GROUP BY
reseller_id ORDER BY sold_count ASC

上述查询将选择所有商品，并按每个经销商对它们进行分组，包括商品总数和退款计数。粗体部分是错误的，我想获得总价（这是未退款+每个经销商出售的总价（注意我按经销商 ID 分组）

原文

i'm trying to build a query to check my stock.

SELECT COUNT(*) AS item_count,
reseller_id, sum(sold) as
sold_count, sum(refunded) as
refunded_count,**sum(case price when
refunded <> 1 AND sold=1) as pricesum** FROM stock
GROUP BY
reseller_id ORDER BY sold_count ASC

The above query will select all items and group them by each reseller with total items and refund count. The part in bold is wrong i want to get the total price(which is the sum for the none refunded + sold for each of the resellers (notice i group by reseller id)

分享到QQ

分享到微博

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

发布评论

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

野の 2024-11-09 00:45:16

像这样的 case 语句可能会起作用：

SUM(CASE
    WHEN refunded <> 1 AND sold = 1
        THEN price
    ELSE
        0
    END)

Something like this with the case statements might work:

SUM(CASE
    WHEN refunded <> 1 AND sold = 1
        THEN price
    ELSE
        0
    END)

回复收藏 0 原文

回心转意 2024-11-09 00:45:16

我注意到的事情：

你的案子还没有结束。只需写“end”即可完成
您还没有给出其他情况

回复收藏 0 原文

荭秂 2024-11-09 00:45:16

尝试创建如下子查询：

SELECT SUM(price) FROM stock WHERE returneded != 1 AND sell = 1

完整的查询如下所示：

SELECT
  COUNT(*) AS item_count,
  reseller_id,
  SUM(sold) as sold_count,
  SUM(refunded) as refunded_count,
  (SELECT SUM(price) FROM stock WHERE refunded != 1 AND sold = 1 WHERE reseller_id=1 GROUP BY reseller_id) as pricesum
FROM stock
WHERE reseller_id=1
GROUP BY reseller_id
ORDER BY sold_count ASC

Try to make a subquery like:

SELECT SUM(price) FROM stock WHERE refunded != 1 AND sold = 1

The complete query would look like this:

SELECT
  COUNT(*) AS item_count,
  reseller_id,
  SUM(sold) as sold_count,
  SUM(refunded) as refunded_count,
  (SELECT SUM(price) FROM stock WHERE refunded != 1 AND sold = 1 WHERE reseller_id=1 GROUP BY reseller_id) as pricesum
FROM stock
WHERE reseller_id=1
GROUP BY reseller_id
ORDER BY sold_count ASC

回复收藏 0 原文

~没有更多了~