2

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?

Ben
  • 51,770
  • 36
  • 127
  • 149
user1709570
  • 21
  • 2
  • Hi and welcome to Stack Overflow. If you have a question please put _all_ relevant information in your question; not on an external site. – Ben Sep 30 '12 at 16:31
  • I _think_ you're looking for the sum of the stoptimes for the machineid, code combination with the greatest count? Can you please update your question to give a description of what you would like? – Ben Sep 30 '12 at 16:47
  • Yes Ben,if yuo look at CCM61 you will see 2 record with the code 130 with a SopTime of 10 and 30. My query should retrive MachineID=CCM61 , Code=130 , StopTime=40 , Num=2. For CCM66 (code 110) the query must retrive : MachineID=CCM66 , Code=110 , StopTime=15 , Num=2. In few words , for each MachineID I wan retreive, for each different Code, the StopTime Sum and the its occurence number. – user1709570 Sep 30 '12 at 17:13

2 Answers2

2

Something like following will work;

SELECT ... SUM(StopTime), COUNT(*) as Num FROM ... GROUP BY MACHINEID, CODE
Turcia
  • 653
  • 1
  • 12
  • 29
1

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

Sami
  • 8,168
  • 9
  • 66
  • 99
  • @user1709570 Snapshots are good but providing your table structure and data makes it pretty much explained/clear e.g. you can have a look at this question http://stackoverflow.com/questions/12653678/group-concat-concat-not-working-with-null-and-not-null-values – Sami Sep 30 '12 at 18:50