SQL Select aggregate values in column

Question

Hello

I'm curious, is this result set can be achieved with sql query?

Here is the 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 could the query 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

Without knowing how you want to use the results, it may be worth considering if a simpler, similar query can be used. For example:

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 close results that may be just as usable in your implementation(?).

Otherwise, you could also use that query/result set as a base View (eg. calling it vw_Base) then do something like this (note: totally 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

And, depending on the database system you're using, use a function to convert any null results returned to zeros (e.g. ISNULL() in SQL Server).