尝试根据不同字段的值计算字段的 # 个不同值 [英] Trying to count # distinct values of a field based on value of a different field

问题描述

正在寻找有关我的 SQL 查询的帮助.我试图找到一种方法来指定客户在两家不同商店购买或销售的情况，但如果他们在一家商店购买并在另一家商店出售，我不在乎或不希望这被计算在内.

Looking for some help with my SQL query. I am trying to find a way to specify cases in which a customer purchases or sells at two different stores, but if they purchase at one store and sell at another, I don't care or want that to count.

我已经试过了 -

Select count(distinct store) OVER(Partition BY Customer)

但它不喜欢不同的并导致错误.当我不指定 distinct 时，它会给我该客户所有观察的计数，而不仅仅是他们购买或出售的商店数量的计数.

but it doesn't like the distinct and causes an error. When I dont specify distinct, it will give me the count of all observations of that customer, instead of just the count of # of stores that they purchased from, or sold to.

根据以下数据，客户 D 是我要过滤的类型.

Based on the data below, customer D is the type im looking to filter for.

我的原始数据:

Customer    Type        Qty     Store
A           Purchase    1       2
A           Purchase    2       2
A           Sale        3       1
B           Sale        24      1
B           Sale        12      1
C           Purchase    4       2
D           Sale        12      2
D           Purchase    4       2
D           Purchase    2       1
D           Purchase    2       1

有什么想法吗?

推荐答案

select customer
from your_table
group by customer, type
having count(distinct store) > 1

这篇关于尝试根据不同字段的值计算字段的 # 个不同值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！