MySQL query to get the total occureences across columns of a row.

You may want to transform this table to 3rd normal form
IP, timestamp, fan-id fan-state
192.168.1.1,  YYMMDDhhmmss, fan1, operational
192.168.1.1,  YYMMDDhhmmss, fan2, failed
192.168.1.1,  YYMMDDhhmmss, fan3, failed
192.168.1.1,  YYMMDDhhmmss, fan4 , operational
192.168.1.1,  YYMMDDhhmmss, fan5, operational
192.168.1.1,  YYMMDDhhmmss, fan6 , operational
192.168.1.1,  YYMMDDhhmmss, fan7, failed
192.168.1.1,  YYMMDDhhmmss, fan8, operational

select  count(*) where IP = 192.168.1.1, ts=...., fan-state=failed

would supply your count. This can be combined with a query enumerating IP & Timestamp to provide all data.

Unfortunately the backend guy set up the DB that way :(

Here is a way using a cursor. If you're not dealing with a long list of ip addresses, then the performance of the cursor shouldn't be an issue.

declare @ip nvarchar(15)
            ,@fan1Count int
            ,@fan2Count int
            ,@fan3Count int
            ,@fan4Count int
            ,@fan5Count int
            ,@fan6Count int
            ,@fan7Count int
            ,@fan8Count int
            ,@countValue nvarchar(15) = 'failed'

declare @TotalCounts table (ip nvarchar(15), TotalCount int)
            
declare ipCursor cursor for
select distinct ip from ExpertsExchange.dbo.Table1

open ipCursor

fetch next from ipCursor into @ip

while @@FETCH_STATUS = 0
begin
      select @fan1Count = sum (case when fan1 = @countValue then 1 else 0 end) from ExpertsExchange.dbo.Table1 where ip = @ip
      select @fan2Count = sum (case when fan2 = @countValue then 1 else 0 end) from ExpertsExchange.dbo.Table1 where ip = @ip
      select @fan3Count = sum (case when fan3 = @countValue then 1 else 0 end) from ExpertsExchange.dbo.Table1 where ip = @ip
      select @fan4Count = sum (case when fan4 = @countValue then 1 else 0 end) from ExpertsExchange.dbo.Table1 where ip = @ip
      select @fan5Count = sum (case when fan5 = @countValue then 1 else 0 end) from ExpertsExchange.dbo.Table1 where ip = @ip
      select @fan6Count = sum (case when fan6 = @countValue then 1 else 0 end) from ExpertsExchange.dbo.Table1 where ip = @ip
      select @fan7Count = sum (case when fan7 = @countValue then 1 else 0 end) from ExpertsExchange.dbo.Table1 where ip = @ip
      select @fan8Count = sum (case when fan8 = @countValue then 1 else 0 end) from ExpertsExchange.dbo.Table1 where ip = @ip

      insert into @TotalCounts(ip, TotalCount)
      select @ip, (@fan1Count + @fan2Count + @fan3Count + @fan4Count + @fan5Count + @fan6Count + @fan7Count + @fan8Count)

      fetch next from ipCursor into @ip
end

select * from @TotalCounts
order by ip

p.s.   Sorry... don't forget to include the following just after that last end statement

close ipCursor
deallocate ipCursor

That de-normalized data structure will be painful to analyze, perhaps you should create a view so that you can look at the data in a better way for queries?

By the way, I really do hope the "backend guy" has not named a column "timestamp" this will also be painful.

CREATE TABLE Table1
    (`ip` varchar(11), `timestamp` timestamp, `fan1` varchar(11), `fan2` varchar(6), `fan3` varchar(6), `fan4` varchar(11), `fan5` varchar(11), `fan6` varchar(11), `fan7` varchar(6), `fan8` varchar(11))
;
    
INSERT INTO Table1
    (`ip`, `timestamp`, `fan1`, `fan2`, `fan3`, `fan4`, `fan5`, `fan6`, `fan7`, `fan8`)
VALUES
    ('192.168.1.1', '2018-04-16 13:24:35', 'operational', 'failed', 'failed', 'operational', 'operational', 'operational', 'failed', 'operational')
;

Select allOpen in new window

This style of query will "flip" the data into a more "normalized" manner:

create view t1_normalized as 
    select ip, `timestamp`, 1 as fan, case when fan1='failed' then 0 else 1 end as status from table1 union all
    select ip, `timestamp`, 2 as fan, case when fan2='failed' then 0 else 1 end as status from table1 union all
    select ip, `timestamp`, 3 as fan, case when fan3='failed' then 0 else 1 end as status from table1 union all
    select ip, `timestamp`, 4 as fan, case when fan4='failed' then 0 else 1 end as status from table1 union all
    select ip, `timestamp`, 5 as fan, case when fan5='failed' then 0 else 1 end as status from table1 union all
    select ip, `timestamp`, 6 as fan, case when fan6='failed' then 0 else 1 end as status from table1 union all
    select ip, `timestamp`, 7 as fan, case when fan7='failed' then 0 else 1 end as status from table1 union all
    select ip, `timestamp`, 8 as fan, case when fan8='failed' then 0 else 1 end as status from table1
;

Select allOpen in new window

You can "tune" that view query to produce the columns you want. Once that is done then, use that view in the final query:

select ip, timestamp, sum(status) 
from t1_normalized
group by ip, timestamp
;

Select allOpen in new window

You don't state what you really want the query to produce so I don't know if the grouping should include "timestamp" or not.

nb. You might want to "truncate" the timestamp to represent just a day (not time of day) in the view

I dont need the timestamp in the result just the total cols where the fan = failed.

I cant use cursors, or views. Has to be one sql query.

Surely there must be some easier way to do this then the options presented?

Looks like its only counting the operational cols, hence 5.