'How to find the mode of 3 columns in SQL?

In SQL I have a table with 3 columns:

Month1 Month2 Month3
0 1 0
1 1 1
0 1 1

...and so on.

I need another column where it gives the mode of Month1, Month2 and Month3.

My expected output is:

Month1 Month2 Month3 Mode
0 1 0 0
1 1 1 1
0 1 1 1

So far I have only calculated mode for a single column. Not sure how we can do it horizontally by combining 3 columns.

sqlsql-serverstatistical-mode


Solution 1:[1]

This should work, can easily be expanded for n columns:

select month1, month2, month3, ca.val
from t
cross apply (
    select top 1 val
    from (values
        (month1),
        (month2),
        (month3)
    ) as v(val)
    group by val
    order by count(*) desc
) as ca

For RDBMS other than SQL server, replace values(...) with appropriate table value constructor, cross apply with lateral join/sub query inside select and top 1 with limit/offset...fetch.

Solution 2:[2]

You could use a CASE expression:

SELECT *, CASE WHEN Month1 + Month2 + Month3 <= 1 THEN 0 ELSE 1 END AS mode
FROM yourTable;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Tim Biegeleisen

Related Questions