How to find the mode of 3 columns in SQL?-CodePudding

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.

CodePudding user response：

You could use a CASE expression:

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

CodePudding user response：

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.