Mysql – Help with COUNT across multiple columns

countMySQL

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.

Best Answer

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.

Related Question