A WHERE clause defines conditions that you want to apply to a query.
In a non-aggregate query, WHERE clauses are used instead of HAVING clauses. In an aggregate query, WHERE clauses are used to define conditions that you want to apply to the query before the calculation dictated by the aggregate function has been performed.
You might want to calculate the total failure cost for all pieces of equipment, but you want to include in the calculation only failures with a total failure cost greater than $5,000.00. You could create a query like this:
SELECT [Asset].[ASSET_ID_CHR] "Asset ID", Sum([Failure].[EFAIL_TOTCST_FRM]) "Total Failure Cost"
FROM [Asset] JOIN SUCC [Failure] ON {Asset Has Failure}
WHERE [Failure].[EFAIL TOTCST FRM] > 5000
GROUP BY [Asset].[ASSET ID CHR]
In this query, you can see that the WHERE clause is:
WHERE [Failure].[EFAIL TOTCST FRM] > 5000
This WHERE clause returns the total failure cost for all pieces of equipment, but calculates only the failures with a total failure cost greater than $5,000.00.
A piece of equipment might have failures whose failure costs were $5,050.00, $1,000.00, and $500.00. Because the WHERE clause indicates that you want to return the total failure cost for all pieces of equipment but only calculate the failures with a total failure cost greater than $5,000.00, the total failure cost returned for this piece of equipment would be $5,050.00 (the failures with a cost of $1,000.00 and $500.00 are not included in the calculation because they are less than $5,000.00 each).
Copyright © 1993-2015 Meridium, Inc. All rights reserved.
