SQL 多个 COUNT

Question

让我们考虑一下这个表:

[name] [type]
"Ken Anderson" 1
"John Smith" 2
"Bill Anderson" 1
"George Anderson" 1
"Taylor Smith" 1
"Andrew Anderson" 2
"Dominic Smith" 2

和那个查询:

SELECT mates.type, COUNT(*) AS SmithsCount
FROM mates
WHERE mates.name LIKE "* Smith"
GROUP BY mates.type

结果应该是这样的

[type] [SmithsCount]
1 1
2 2

如果我想在每个组中也 Andersons Count 怎么办？喜欢

[type] [SmithsCount] [AndersonsCount]
1 1 3
2 2 1

当然，我希望它尽可能简单 ;) 我是 SQL 的新手，我阅读了 W3 Schools 和 http://www.sql-tutorial.net/ 上的教程。但是只有很少的示例基础知识，任何“更多”复杂的查询。有人有一些有用的链接吗？谢谢。

Answer 1

select type,
       sum(case when name like '% Smith' then 1 else 0 end) as SmithCount,
       sum(case when name like '% Anderson' then 1 else 0 end) as AndersonCount
    from mates
    group by type