SQL Query count of values

Question

I'm trying to write a query that will show the number of times some specific values are present per node

Here is my table:

Host_Name   Node_Name   Channel_Width
-------------------------------------
Host_1       AAA         32
Host_1       AAA         64
Host_1       AAA         64
Host_1       AAA         64
Host_1       AAA         64
Host_1       AAB         32
Host_1       AAB         32
Host_1       AAB         64
Host_1       AAB         64
Host_1       AAB         96
Host_2       AAC         32
Host_2       AAC         64
Host_2       AAC         64
Host_2       AAC         64
Host_2       AAC         64
Host_2       AAC         64
Host_2.      AAD         64
Host_2       AAD         64
Host_2       AAD         64

I'm trying to query the count of nodes for the number of times the values in channel_width show up. For instance, the amount of nodes '32' show up once and '64' shows up 3 times, the amount of nodes '64' shows up all 4 times but '32' never shows up, etc. Ultimately, I'm trying to get the count of nodes for each circumstance per host_name. If the value doesn't show up, I don't want it counted as part of the count.

This is what I tried but its showing the count the same across the board which isn't right...

SELECT host_name,
       node_name,
       COUNT(channel_width = '32') AS 3_MHz,
       COUNT(channel_width = '64') AS 6_MHz,
       COUNT(channel_width='96') AS SOFA,
FROM table_name
GROUP BY host_name, node_name

Thanks!

EDIT: expected results:

Host_Name| Node_Name | 3 MHz | 6 MHz | SOFA
---------+-----------+-------+-------+------
Host_1   | AAA       | 1     | 4     | 0
Host_1   | AAB       | 2     | 2     | 1

user_12458846 · Answer

Please try this out

SELECT host_name,
       node_name,
       count(CASE WHEN channel_width = '32' THEN 1 END) AS 3_MHz,
       count(CASE WHEN channel_width = '64' THEN 1 END) AS 6_MHz,
       count(CASE WHEN channel_width = '96' THEN 1 END) AS SOFA
FROM details
group by host_name, node_name

Check the db fiddle here

user_19379859 · Answer

With CTE As (
        Select Host_Name , Node_Name ,
        Case when Channel_width=32 then '3_MHz'
             when Channel_width=64 then '6_MHz'
             Else 'SOFA'
             End As AAA,
        Channel_width
From Table_1
)
Select *  From CTE

Pivot (Count(Channel_Width) For AAA
   In ([3_MHz] , [6_MHz] , [SOFA])) As MyPivot1

SQL Query count of values

2 Answers