In an aggregate query, HAVING clauses are used instead of WHERE clauses. HAVING clauses are used to define conditions that you want to apply to each aggregate value after the calculation dictated by the aggregate function has been performed.
A HAVING clause defines conditions that you want to apply to an aggregate query.
In an aggregate query, HAVING clauses are used instead of WHERE clauses. HAVING clauses are used to define conditions that you want to apply to each aggregate value after the calculation dictated by the aggregate function has been performed.
You might want to see all pieces of equipment whose failures resulted in a total failure cost greater than $5,000.00. To do so, you could create a query like this:
SELECT [Asset].[ASSET_ID_CHR] "Asset ID", Sum([Failure].[EFAIL_TOTCST_FRM]) "Total Failure Cost", Count([Failure].[MI_EVENT_ID]) "Failure Count"
FROM [Asset] JOIN SUCC [Failure] ON {Asset Has Failure}
GROUP BY [Asset].[ASSET ID CHR]
HAVING Sum([Failure].[EFAIL TOTCST FRM]) > 5000
In this query, you can see that the HAVING clause is:
HAVING Sum([Failure].[EFAIL TOTCST FRM]) > 5000
This HAVING clause returns pieces of equipment whose failures resulted in a total failure cost greater than $5,000.00.
A piece of equipment might have failures whose failure costs were $3000.00, $1,000.00, and $500.00. If you add these values together, you can see that the total failure cost for failures associated with the piece of equipment is $4,500.00. Because the HAVING clause indicates that you want to return only pieces of equipment with a total failure cost greater than $5,000.00, this piece of equipment would not be returned.
Copyright © 2018 General Electric Company. All rights reserved.