如何选择总和大于 x 的数据 [英] How to select data where sum is greater than x

问题描述

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

I am very new to SQL and am using SQLite 3 to run basket analysis on sales data.

相关列是产品 ID、唯一交易 ID(标识购物篮)和产品数量.如果客户购买了不止一种产品类型，则重复唯一交易 ID.

The relevant columns are the product ID, a unique transaction ID (which identifies the basket) and the product quantity. Where a customer has bought more than one product type, the unqiue transaction ID is repeated.

我只想选择客户购买了超过 1 件商品的篮子.

I am wanting to select only baskets where the customer has bought more than 1 item.

SQLite 有没有办法选择唯一交易 ID 和数量总和，但只针对数量大于 1 的唯一交易 ID?

Is there any way on SQLite to select the unique transaction ID and the sum of the quantity, but only for unique transaction IDs where the quantity is more than one?

到目前为止我已经尝试过:

So far I have tried:

select uniqID, sum(qty) from salesdata where sum(qty) > 1 group by uniqID;

但是 SQLite 给了我错误'滥用聚合:sum()'

But SQLite gives me the error 'misuse of aggregate: sum()'

对不起，如果这是一个简单的问题，但我正在努力通过谷歌搜索找到任何相关信息！

Sorry if this is a simple question but I am struggling to find any relevant information by googling!

推荐答案

尝试

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

"where" 不能用于聚合函数 - 在这种情况下，您只能在 uniqId 上使用 where.

"where" cannot be used on aggregate functions - you can only use where on uniqId, in this case.

这篇关于如何选择总和大于 x 的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！