1

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.

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.

0
1

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.