SQL:查看售出产品的捆绑包

Question

我有一个下面的示例数据库。我想看看我们卖了多少电视和网络捆绑包。在样本数据中，只有Bob和Trevor销售该捆绑包，所以我们卖出了2个。

如何编写查询每个销售代表售出的捆绑包数量和捆绑包销售总价的查询？

谢谢

​

​

Answer 1

你可以简单地通过计算他们销售的不同产品来对推销员进行分组-

SELECT Sales_Person, FLOOR(COUNT(DISTINCT product_name)/2) NO_OF_BUNDLES, sum(total_price)
FROM YOUR_TAB
WHERE product_name IN ('TV', 'Internet')
GROUP BY Sales_Person
HAVING COUNT(DISTINCT product_name) >= 2

Answer 2

我设想，要实现捆绑销售，同一个销售人员需要将两种产品都卖给同一个客户。

我将通过两个级别的聚合来实现这一点。首先在子查询中按销售人员和客户分组，以标识捆绑包，然后在外部查询中，计算每个销售人员发生了多少此类捆绑包：

SELECT sales_person, COUNT(*) bundles_sold, SUM(total_price) total_price
FROM (
    SELECT sales_person, customer_name, SUM(total_price) total_price
    FROM mytable
    WHERE product_name in ('TV', 'Phone')
    GROUP BY sales_person
    HAVING COUNT(DISTINCT product_name) = 2
) x

Answer 3

使用cte的方法如下：

with cte1(sales_person, customer_name, product_count) as
(
select sales_person, customer_name, count(product_name)
from sales
where product_name in ('TV', 'Internet')
group by sales_person, customer_name
having count(product_name) = 2
)
select sales_person, count(product_count)
from cte1
group by sales_person