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

I have a MySQL table w/ the following columns:

ip_address
timestamp
fan1
fan2
fan3
fan4
fan5
fan6
fan7
fan8

The value of fan1 to fan8 cols can either be "failed" or "operational". This is reporting data via snmp OIDs to this table/cols.

I need an SQL query that gets a count/total of the row that it's reading where cols fan1 to fan8 have value "failed". So the total can be 0 to 8.  Normally I would use select count(*) as total, but that counts rows not cols. So I need the total columns value for the current row where fans have failed for that device.

Example:

row1:
ip - 192.168.1.1
timestamp - blahhhhhh...
fan1 - operational
fan2 - failed
fan3 - failed
fan4 - operational
fan5 - operational
fan6 - operational
fan7 - failed
fan8 - operational

So in the example above after running the mysql query the total should be 3 in the output of the query.

I'm not sure how to approach this. I tried doing something using SUM w/ IF statement but couldn't get it working.

Thanks.
P HAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nociSoftware EngineerCommented:
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.
P HAuthor Commented:
Unfortunately the backend guy set up the DB that way :(
Wayne RushingCommented:
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
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

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

close ipCursor
deallocate ipCursor
PortletPaulEE Topic AdvisorCommented:
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')
;

Open 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
;

Open 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
;

Open 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
P HAuthor Commented:
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?
PortletPaulEE Topic AdvisorCommented:
Surely.........

Just re-arrange the parts already provided. Take the view query, and use that instead of the view.... e.g.

select ip, timestamp, sum(status) 
from (
    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
   ) as t1_normalized
group by ip, timestamp

Open in new window

If you don't want the timestamp involved then remove it from that query...
(Surely that you can do?)
P HAuthor Commented:
Looks like its only counting the operational cols, hence 5.

Screen-Shot-2018-04-17-at-11.17.32-A.png
P HAuthor Commented:
Ok I see the problem. I adjusted query.

select ip, timestamp, sum(status) \
from ( \
    select ip, `timestamp`, 1 as fan, case when fan1='failed' then 1 else 0 end as status from Table1 union all \
    select ip, `timestamp`, 2 as fan, case when fan2='failed' then 1 else 0 end as status from Table1 union all \
    select ip, `timestamp`, 3 as fan, case when fan3='failed' then 1 else 0 end as status from Table1 union all \
    select ip, `timestamp`, 4 as fan, case when fan4='failed' then 1 else 0 end as status from Table1 union all \
    select ip, `timestamp`, 5 as fan, case when fan5='failed' then 1 else 0 end as status from Table1 union all \
    select ip, `timestamp`, 6 as fan, case when fan6='failed' then 1 else 0 end as status from Table1 union all \
    select ip, `timestamp`, 7 as fan, case when fan7='failed' then 1 else 0 end as status from Table1 union all \
    select ip, `timestamp`, 8 as fan, case when fan8='failed' then 1 else 0 end as status from Table1 \
   ) as t1_normalized \
group by ip, timestamp;

Open in new window

This seems to work.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.