SQL Select aggregated values ​​in a column

Question

I am curious if this result can be achieved with sql query?

Here is a table

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

here is the result

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

What does the request look like?

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

Answer 2

Not knowing how you want to use the results, it might be worth considering whether a simpler similar query can be used. For instance:

SELECT name, availability, COUNT(*) As CountOfAvailability
GROUP BY name, availability

Will generate:

name         availability      CountOfAvailability
---------------------------------------------------
brush        available          2
paint        available          1
paint        not available      1

This gives you a good set of related results that can be just as useful in your implementation (?).

Otherwise, you can also use this query / result set as the base view (e.g. calling it vw_Base), then do something like this (note: completely untested):

SELECT DISTINCT
   names.name,
   available_results.CountOfAvailability As "available",
   not_available_results.CountOfAvailability As "not available"
FROM 
vw_Base
    LEFT JOIN (SELECT CountOfAvailability FROM vw_Base WHERE availability = 'available') As available_results ON vw_Base.name = available_results.name
    LEFT JOIN (SELECT CountOfAvailability FROM vw_Base WHERE availability = 'not available') As not_available_results ON vw_Base.name = not_available_results.name

, , , (, ISNULL() SQL Server).