Help with COUNT across multiple columns

Question

I've had a look around and a play and can't what I need to work so wondered if you could help?

I have four rows in a table and I wish to count the number of items across all four. So to make it clearer the table looks like this:

Table: 'faults'
Row 1: affected_device_1
Row 2: affected_device_2
Row 3: affected_device_3
Row 4: affected_device_4

Each row can be populated with an entry. I wish to collate and put into a report the top five "affected devices" so that someone higher up can look at it and make a decision. So in plain English the result would return something like:

The top five affected devices are:

1. X
2. XX
3. XXX
4. XXXX
5. XXXXX

The SQL code to create the table is as follows:

CREATE TABLE `faults` (
 `fault_id`int(6) NOT NULL AUTO_INCREMENT,
 `affected_device_1` varchar(50) NOT NULL,
 `affected_device_2` varchar(50) NOT NULL,
 `affected_device_3` varchar(50) NOT NULL,
 `affected_device_4` varchar(50) NOT NULL,
 PRIMARY KEY (`fault_id`))

Then I insert a bunch of data into each of the four columns:

INSERT INTO `faults`
(
 `fault_id`, `affected_device_1`, `affected_device_2`, 
 `affected_device_3`, `affected_device_4`
) 
VALUES
(1, 'device1-name', 'device2-name', 'device3-name', 'device4-name'),
(1, 'device3-name', 'device1-name', 'device2-name', 'device4-name'),
(1, 'device4-name', 'device3-name', 'device1-name', 'device2-name'),

Each 'fault' can have up to four 'affected devices' associated with it. I want to then count what are the top 'affected devices' across all four columns. Which devices occurs the most and is therefore most problematic.

Sorry if I haven't made it any clearer. The problem I suspect is that the design is rubbish so please bear with me.

Answer 1

Given the new information (you should probably edit the question, not add an answer, but I assume you are not entitled to). First I don't understand the database design, do you always get 4 affected devices per fault? Anyhow, you can transpose your table like:

select affected_device_1 from faults
union all
select affected_device_2 from faults
union all
select affected_device_3 from faults
union all
select affected_device_4 from faults

I won't be efficient but it is the best I can think of. From there you can count the number of occurrences for each device:

select affected_device, count(1)
from (
 select affected_device_1 as affected_device from faults
 union all
 select affected_device_2 as affected_device from faults
 union all
 select affected_device_3 as affected_device from faults
 union all
 select affected_device_4 as affected_device from faults
) as T
group by affected_device;

Note that if a fault contains the same device twice the count will be wrong.

Answer 2

This presents the top 5 bad devices as you requested

SET @lineitem = 0;
SET @top = 5;
SELECT * FROM
(
 SELECT (@lineitem:=@lineitem+1) line,affected_device,device_count FROM
 (
 SELECT affected_device,count(1) device_count FROM
 (
 SELECT affected_device_1 affected_device FROM faults
 UNION ALL
 SELECT affected_device_2 FROM faults
 UNION ALL
 SELECT affected_device_3 FROM faults
 UNION ALL
 SELECT affected_device_4 FROM faults
 ) AAA GROUP BY affected_device
 ) AA ORDER BY device_count DESC
) A WHERE line <= @top;

Answer 3

You should edit the question because what actually you want from faults table which has not cleared.

Case#1

As per your query at first you statement if you want to number of items of all four rows that is affected_device_1,affected_device_2,affected_device_3,affected_device_4 and all of them comes under the affected_device column then the query will be

Select * 
from faults 
where affected_device IN ('affected_device_1'
 ,'affected_device_2'
 ,'affected_device_3'
 ,'affected_device_4');

The output like in this fashion

affected_device(Column1) column2 column3 column4 column5
affected_device_1 column2 column3 column4 column5
affected_device_2 column2 column3 column4 column5
affected_device_3 column2 column3 column4 column5
affected_device_4 column2 column3 column4 column5

Case#2 If you want to display all affected_device values with rows as well as column then your query will be

Select * 
from faults 
where affected_device REGEXP '^affected_device';

or

Select * 
from faults 
where affected_device like '%affected_device';

The output will be shows all rows as well as columns based as on affected_device and you shall choose your top five affected device from there.

affected_device(Column1) column2 column3 column4 column5
affected_device_1 column2 column3 column4 column5
affected_device_2 column2 column3 column4 column5
affected_device_3 column2 column3 column4 column5
affected_device_4 column2 column3 column4 column5
affected_device_5 column2 column3 column4 column5

I hope it will be help to you.