SQL 选择列中的聚合值

Question

我很好奇，这个结果集可以用sql查询实现吗？

这是 table

item_id    name       availability
-----------------------------------
1          brush      available
2          brush      available
3          paint      not available
4          paint      available

这是结果

name         available    not available
---------------------------------------
brush        2            0
paint        1            1

查询会是什么样子？

Answer 1

SELECT  name,
        COALESCE(SUM(CASE WHEN availability = 'available' THEN 1 ELSE 0 END), 0) AS available,
        COALESCE(SUM(CASE WHEN availability = 'available' THEN 0 ELSE 1 END), 0) AS not_available
FROM    mytable
GROUP BY
        name