CODEWITHSUNDEEP
mysqlsql
user1709570
user1709570

Reputation: 21

MySQL query to find similar but not duplicated row

I need a query to retrieve machinery stop codes and failure descriptions that looks like a duplicate search query but it is quite different.

Here's my source table

enter image description here

I'm using this query

SELECT `TestDups`.`MachineID`
     , `TestDups`.`Code`
     , `TestDups`.`StopTime`
  FROM `TestDups`
 WHERE `TestDups`.`Code` IN ( SELECT `TestDups`.`Code`
                                FROM `TestDups`
                               GROUP BY `TestDups`.`Code`
                              HAVING COUNT(*) > 0 )
 ORDER BY `TestDups`.`MachineID`

which returns this:

enter image description here

but I would like it to return this:

enter image description here

How can I reach my goal?

Upvotes: 2

Views: 142

Answers (2)

Sami
Sami

Reputation: 8419

Hope this query would produce your desired result. I have given link for the demo as well

select MachineID,Code,sum(stoptime) as StopTime, count(*) as Num from 
TestDups group by MachineID,code order by machineid

Link for SQL Fiddle Demo

Upvotes: 1

Turcia
Turcia

Reputation: 711

Something like following will work;

SELECT ... SUM(StopTime), COUNT(*) as Num FROM ... GROUP BY MACHINEID, CODE

Upvotes: 2

Related Questions