Reputation: 58
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
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
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
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
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