时间戳

首页 > 解决方案 > SQL 计数不同的输出不是所希望的

sql - SQL 计数不同的输出不是所希望的

问题描述

%%sql

SELECT 
    COUNT(DISTINCT c.email) CountRockListeners
     
FROM
    customer c
         JOIN invoice i ON c.customer_id = i.customer_id
         JOIN invoice_line il ON i.invoice_id = il.invoice_id
         JOIN track t ON il.track_id = t.track_id
         JOIN genre g ON t.genre_Id = g.genre_Id
WHERE g.name = 'Rock' OR g.name = 'Pop'

我有这个 SQL 代码，想返回 Rock 和 Pop 监听器的计数，但它只返回 Rock 的计数，即 59 .Pop 是 32。所以我希望它返回 59+32 请指教？

标签： sqlsqlite

解决方案

也许你想要条件聚合：

SELECT COUNT(DISTINCT c.email) as cnt_either
       COUNT(DISTINCT CASE WHEN g.name = 'Rock' THEN c.email END) as cnt_rock,
       COUNT(DISTINCT CASE WHEN g.name = 'Pop' THEN c.email END) as cnt_pop
FROM customer c JOIN
     invoice i
     ON c.customer_id = i.customer_id JOIN
     invoice_line il
     ON i.invoice_id = il.invoice_id JOIN
     track t
     ON il.track_id = t.track_id JOIN
     genre g
     ON t.genre_Id = g.genre_Id
WHERE g.name IN ('Rock', 'Pop')

推荐阅读