需要有關 Teradata 中 SQL 查詢的幫助

我有一個包含以下列的表格

member_number
step_count 
social_activity -- (which contains both Challenges and Posts)
high_fives

基本上所有第 2-4 列都是成員的計數。

我想創建一個報告，我可以在其中顯示member_number、step_count、social_activity（它是“挑戰”）、social_activity（它是“文章”）和high_fives。

我寫了查詢

select member_number, step_count, social_activity
from ABC
group by member_number

但我希望查詢獲得social_activity（‘challenges’）和social_activity（‘posts’）的單獨計數。

有人可以幫我弄這個嗎？

似乎您需要基於 CASE的條件總和：

select member_number,
  sum(case when social_activity = 'challenges' then step_count else 0 end) as challenges,
  sum(case when social_activity = 'posts' then step_count else 0 end) as posts
from ABC
group by member_number

您沒有指定是否要計算 step_count 中的行數或求和。我假設它包含先前計算的計數，因此我使用了 SUM 函式。它與 count() 而不是 sum() 相同。如果您需要將 high_fives 添加為一般總計（在成員級別，而不是在活動類型級別，您需要執行外部查詢。試試這個。這在 SQL Fiddle 中不起作用，因為 MySQL 不支持完全外部連接，但它應該在 Teradata 中工作。

select a.member_number, sum(challenges), sum(posts), sum(high_fives) from (
select a.member_number, Challenges, Posts from
(select member_number, sum(step_count) as Challenges from activity where social_activity ='Challenge' group by member_number) a 
full outer join 
(select member_number, sum(step_count) as Posts from activity where social_activity ='Post' group by member_number) b on a.member_number =  b.member_number group by a.member_number
) a inner join activity b on a.member_number = b.member_number
group by a.member_number

引用自：https://dba.stackexchange.com/questions/115259