当前位置：文江博客话题详情

如何计算总和大于x的数据

发布于 2025-01-02 08:58:38 字数 609 浏览 0 评论 0原文

我对 SQL 非常陌生，正在使用 SQLite 3 对销售数据运行购物篮分析。

相关列是产品 ID、唯一交易 ID（标识购物篮）和产品数量。如果客户购买了多种产品类型，则重复唯一的交易 ID。

我想计算顾客购买了 1 件商品的篮子数量。

到目前为止，我已经尝试 select count(distinct uniqID) from salesdatahaving sum(qty) = 1;

但这引发了一个错误，指出 在 HAVING 之前需要 GROUP BY 子句 。

然后我尝试 select count(distinct uniqID) from salesdata group by uniqID has sum(qty) = 1

SQlite 接受了这一点，但返回了一个只有 1 的列表，这也是不对的！

然后我尝试 select count(uniqID) from salesdata group by qtyhaving sum(qty) = 1

SQlite 也接受了这一点，但什么也没返回。

任何想法将不胜感激！

乙

原文

分享到QQ

分享到微博

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

发布评论

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

勿忘初心 2025-01-09 08:58:38

请尝试这样的操作来检索购物篮中拥有多于或等于一件商品的每个用户

select uniqID, sum(qty) as total from salesdata group by uniqID having total >= 1

如果您只想让购物篮中有 1 件商品的用户将 >=1 替换为 ， =1

例如：

select uniqID, sum(qty) as total from salesdata group by uniqID having total = 1

如果您想了解购物篮中有 1 件商品的用户数量，您可以得到如下结果：

SELECT COUNT(*) FROM (select uniqID, sum(qty) as total from salesdata group by uniqID having total 
= 1)

Try something like this to retrieve every user which has more or equal than one item in his basket

select uniqID, sum(qty) as total from salesdata group by uniqID having total >= 1

if you want to have only the users which have 1 item in their baskets replace >=1 with =1

like:

select uniqID, sum(qty) as total from salesdata group by uniqID having total = 1

If you want the numbers of users with 1 item in their baskets you get this like this:

SELECT COUNT(*) FROM (select uniqID, sum(qty) as total from salesdata group by uniqID having total 
= 1)

回复收藏 0 原文

高跟鞋的旋律 2025-01-09 08:58:38

选择仅包含一件商品的购物篮数量。这还将过滤掉数量高于一对一单品数量的购物篮。如果您不想这样做，请删除 WHERE qty = 1 部分。

  SELECT 
    COUNT(uniqID)    FROM 
    (SELECT 
      uniqID, SUM(qty) AS total 
     FROM 
      salesdata 
     WHERE 
       qty = 1 
     GROUP BY 
       uniqID 
     HAVING 
       total = 1)

Selecting the number of baskets that have only one item. This will also filter out baskets with a quantity higher than one on one single item. If you don't want that, remove the WHERE qty = 1 part.

  SELECT 
    COUNT(uniqID)    FROM 
    (SELECT 
      uniqID, SUM(qty) AS total 
     FROM 
      salesdata 
     WHERE 
       qty = 1 
     GROUP BY 
       uniqID 
     HAVING 
       total = 1)

回复收藏 0 原文

~没有更多了~