[SOLVED] Query'd top 15 faults, need the accumulated downtime from another column

Query'd top 15 faults, need the accumulated downtime from another column

I'm currently trying to query up a list of the top 15 occurring faults on a PLC in the warehouse. I've gotten that part down:

Select top 15 fault_number, fault_message, count(*) FaultCount
from Faults_Stator
where T_stamp> dateadd(hour, -18, getdate())
Group by Fault_number, Fault_Message
Order by Faultcount desc

HOOOWEVER I now need to find out the accumulated downtime of said faults in the top 15 list, information in another column "Fault_duration". How would I go about doing this? Thanks in advance, you've all helped me so much already.

+--------------+---------------------------------------------+------------+
| Fault Number |                Fault Message                | FaultCount |
+--------------+---------------------------------------------+------------+
|          122 | ST10: Part A&B Failed                       |         23 |
|            4 | ST16: Part on Table B                       |         18 |
|            5 | ST7: No Spring Present on Part A            |         15 |
|            6 | ST7: No Spring Present on Part B            |         12 |
|            8 | ST3: No Pin Present B                       |          8 |
|            1 | ST5: No A Housing                           |          5 |
|           71 | ST4: Shuttle Right Not Loaded               |          4 |
|          144 | ST15: Vertical Cylinder did not Retract     |          3 |
|           98 | ST8: Plate Loader Can not Retract           |          3 |
|           72 | ST4: Shuttle Left Not Loaded                |          2 |
|           94 | ST8: Spring Gripper Cylinder did not Extend |          2 |
|           60 | ST8: Plate Loader Can not Retract           |          1 |
|           83 | ST6: No A Spring Present                    |          1 |
|            2 | ST5: No B Housing                           |          1 |
|           51 | ST4: Vertical Cylinder did not Extend       |          1 |
+--------------+---------------------------------------------+------------+

I know I wouldn't be using the same query, but I'm at a loss at how to do this next step.

Fault duration is a column which dictates how long the fault lasted in ms. I'm trying to have those accumulated next to the corresponding fault. So the first offender would have those 23 individual fault occurrences summed next to it, in another column.

Solution

You should be able to use the SUM accumulator:

Select top 15 fault_number, fault_message, count(*) FaultCount, SUM (Fault_duration) as FaultDuration
from Faults_Stator
where T_stamp> dateadd(hour, -18, getdate())
Group by Fault_number, Fault_Message
Order by Faultcount desc