Ahmed Ansari
Ahmed Ansari

Reputation: 58

SQL Count with Case

I'm trying to do something like this.

signal_status    signal_count
---------------|---------------
Average        |    10   
Good           |    11  
Very Poor      |     4  
Poor           |     5  
Below Average  |     8  
Excellent      |    13

Total devices are 51 which count should be in that way as defined above in signal_count.

What I've achieved is got signal_status (only the first column). Now I want the count as well but unable to get the count on the basis of devices. This is the query I tried to get the first column.

select signal_status from (select case 
when signal_info <= 31 and signal_info > 24 then 'Excellent' 
when signal_info <= 24 and signal_info > 19 then 'Good' 
when signal_info <= 19 and signal_info > 14 then 'Average' 
when signal_info <= 14 and signal_info > 9 then 'Below Average' 
when signal_info <= 9 and signal_info > 5 then 'Poor' 
when signal_info <= 5 and signal_info > 0 then 'Very Poor' end 
AS signal_status from data_channel) AS WTH group by signal_status;

I tried all possible ways but unable to fetch the desired results. data_channel table contains following columns: sno (PK), device_id, signal_info, datetime and i want it to be joined with table_1 to join on device_id. table_1 contains the following columns: device_id (PK), device_name

What basically I'm trying to do is I want the count for all the devices having signal strength as good average or poor, but what I'm getting is total records from the table not on the basis of recent device signal_info. Sorry for anything if unclear.

Note: Using SQL Server not MySQL

Upvotes: 0

Views: 119

Answers (4)

gotqn
gotqn

Reputation: 43636

Try this:

SELECT signal_status, COUNT(device_id) signal_count 
FROM 
(
    SELECT CASE 
                WHEN signal_info <= 31 and signal_info > 24 THEN 'Excellent' 
                WHEN signal_info <= 24 and signal_info > 19 THEN 'Good' 
                WHEN signal_info <= 19 and signal_info > 14 THEN 'Average' 
                WHEN signal_info <= 14 and signal_info > 9 THEN 'Below Average' 
                WHEN signal_info <= 9 and signal_info > 5 THEN 'Poor' 
                WHEN signal_info <= 5 and signal_info > 0 THEN 'Very Poor' 
            END signal_status
           ,device_id
           ,ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY [datetime] DESC) AS [RowID]
    FROM data_channel
) AS WTH
WHERE [RowID] = 1
GROUP BY signal_status

Upvotes: 3

Nayanish Damania
Nayanish Damania

Reputation: 652

Try this out:

SELECT signal_status, COUNT(DISTINCT device_id) signal_count FROM (
    SELECT 
        CASE 
            WHEN signal_info <= 31 and signal_info > 24 THEN 'Excellent' 
            WHEN signal_info <= 24 and signal_info > 19 THEN 'Good' 
            WHEN signal_info <= 19 and signal_info > 14 THEN 'Average' 
            WHEN signal_info <= 14 and signal_info > 9 THEN 'Below Average' 
            WHEN signal_info <= 9 and signal_info > 5 THEN 'Poor' 
            WHEN signal_info <= 5 and signal_info > 0 THEN 'Very Poor' 
        END signal_status, device_id 
    FROM data_channel
) AS WTH 
GROUP BY signal_status

Upvotes: 1

Atk
Atk

Reputation: 752

Above answers are correct, look sample data and output. If you have a different requirement, provide sample data.

--drop table #data_channel

create table #data_channel
(
sno int identity primary key,
device_id varchar(100), 
signal_info int
)

insert into #data_channel values
('A',24),
('B',14),
('C',24),
('D',31),
('E',1),
('F',13),
('G',20),
('H',12)--,('A',24),('A',24),('A',24),('A',24),('A',24),('A',24),

Query

select signal_status,Count(*) as signalcount
 from (select case 
when signal_info <= 31 and signal_info > 24 then 'Excellent' 
when signal_info <= 24 and signal_info > 19 then 'Good' 
when signal_info <= 19 and signal_info > 14 then 'Average' 
when signal_info <= 14 and signal_info > 9 then 'Below Average' 
when signal_info <= 9 and signal_info > 5 then 'Poor' 
when signal_info <= 5 and signal_info > 0 then 'Very Poor' end 
AS signal_status from #data_channel) AS WTH group by signal_status;

Output

signal_status   signalcount
Below Average   3
Excellent   1
Good    3
Very Poor   1

Upvotes: 0

Mureinik
Mureinik

Reputation: 311326

You're almost there - you're just missing a call to count in your select list:

SELECT   signal_status, 
         COUNT(*) -- Here!
FROM     (SELECT CASE WHEN signal_info <= 31 AND signal_info > 24 THEN 'Excellent' 
                      WHEN signal_info <= 24 AND signal_info > 19 THEN 'Good' 
                      WHEN signal_info <= 19 AND signal_info > 14 THEN 'Average' 
                      WHEN signal_info <= 14 AND signal_info > 9  THEN 'Below Average' 
                      WHEN signal_info <= 9  AND signal_info > 5  THEN 'Poor' 
                      WHEN signal_info <= 5  AND signal_info > 0  THEN 'Very Poor'
                 END  AS signal_status 
          FROM data_channel) AS wth
GROUP BY signal_status;

Upvotes: 2

Related Questions