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:
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.