Group By 但包括“missing"价值观 [英] Group By but include "missing" values

问题描述

假设我有以下内容.

select
  case
    when fcompany = 'Acme' then 'Red'
    when fcompany = 'Acme Rockets' then 'Blue'
    else 'Green'
  end
    Color
,sum(fann_sales)
FROM
  slcdpm
group by
  case
    when fcompany = 'Acme' then 'Red'
    when fcompany = 'Acme Rockets' then 'Blue'
    else 'Green'
  end

假设它经常只返回两种颜色.拉出所有三种颜色并为缺失值包含 0 的最佳方法是什么?

Let's say it often returns with only two colors. What's the best way to pull all three colors and include 0 for the missing value?

联合所有?

推荐答案

将 GROUP 移动到具有更多列的条件 SUM 中?

Move the GROUP into a conditional SUM with more columns?

select
  sum(CASE WHEN fcompany = 'Acme'
                     THEN fann_sales ELSE 0 END) AS redsales,
  sum(CASE WHEN fcompany = 'Acme Rockets'
                     THEN fann_sales ELSE 0 END) AS bluesales
  sum(CASE WHEN fcompany NOT IN ('Acme Rockets', 'Acme')
                     THEN fann_sales ELSE 0 END) AS greensales
FROM
  slcdpm

为此，请跳过桌子.UNION ALL 或子查询方法(在其他答案中)将在每个子句中触及表一次 = 稍慢.

One pass over the table for this. A UNION ALL or subquery approach (in other answers) will touch the table once per clause = somewhat slower.

这篇关于Group By 但包括“missing"价值观的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！