August 20, 2015 at 12:26 pm
Hi,
I've been asked to find the Mode of the loads on trips we run. So I run a count on the [loads] (without grouping, in an analytical function) column, then sort on the highest counts. The problem is when there is a tie. In that case, I need to find the mean of all the tied values. This means that if there is no mode at all, it would simply take the mean of all the values.
Here is sample data:
CREATE TABLE test6
(
trip char(1) NULL,
date int NULL,
loads int NULL
)
;
insert into test6 (trip, date, loads)
values ('A', 1, 15)
insert into test6 (trip, date, loads)
values ('A', 2, 15)
insert into test6 (trip, date, loads)
values ('A', 3, 13)
insert into test6 (trip, date, loads)
values ('A', 4, 12)
insert into test6 (trip, date, loads)
values ('B', 1, 18)
insert into test6 (trip, date, loads)
values ('B', 2, 18)
insert into test6 (trip, date, loads)
values ('B', 3, 16)
insert into test6 (trip, date, loads)
values ('B', 4, 16)
insert into test6 (trip, date, loads)
values ('B', 5, 15)
insert into test6 (trip, date, loads)
values ('C', 1, 13)
insert into test6 (trip, date, loads)
values ('C', 2, 12)
insert into test6 (trip, date, loads)
values ('C', 3, 10)
insert into test6 (trip, date, loads)
values ('C', 4, 9)
and here is what I'd like to see:
CREATE TABLE test7
(
trip char(1) NULL,
loads int NULL
)
insert into test (trip, loads)
values ('A', 15)
insert into test (trip, loads)
values ('B', 17)
insert into test (trip, loads)
values ('C', 11)
Any help is greatly appreciated!!
-Martin
August 20, 2015 at 12:51 pm
This should do the trick. Feel free to ask any questions that you might have.
WITH cteCounts AS(
SELECT trip, loads, COUNT(*) counts
FROM test6
GROUP BY trip, loads
), cteRanks AS(
SELECT trip, loads,
RANK() OVER( PARTITION BY trip ORDER BY counts DESC) rn
FROM cteCounts
)
SELECT trip,
AVG( loads) loads
FROM cteRanks
WHERE rn = 1
GROUP BY trip;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply