Use of the HAVING clause when using muliple sums

Question

I was having a problem getting mulitple sums from multiple tables. Short story, my answer was solved in the "sql sum data from multiple tables" thread on this site. But where it came up short, is that now I'd like to only show sums that are greater than a certain amount. So while I have sub-selects in my select, I think I need to use a HAVING clause to filter the summed amounts that are too low.

Example, using the code specified in the link above (more specifically the answer that the owner has chosen as correct), I would only like to see a query result if SUM(AP2.Value) > 1500. Any thoughts?

score 1 · Answer 1 · answered Nov 03 '11 at 17:05

If you need to filter on the results of ANY aggregate function, you MUST use a HAVING clause. WHERE is applied at the row level as the DB scans the tables for matching things. HAVING is applied basically immediately before the result set is sent out to the client. At the time WHERE operates, the aggregate function results are not (and cannot) be available, so you have to use a HAVING clause, which is applied after the main query is complete and all aggregate results are available.

So... long story short, yes, you'll need to do

SELECT ...
FROM ...
WHERE ...
HAVING (SUM_AP > 1500)

Note that you can use column aliases in the having clause. In technical terms, having on a query as above works basically exactly the same as wrapping the initial query in another query and applying another WHERE clause on the wrapper:

SELECT *
FROM (
   SELECT ... 
) AS child
WHERE (SUM_AP > 1500)

score 0 · Answer 2 · answered Nov 03 '11 at 17:06

You could wrap that query as a subselect and then specify your criteria in the WHERE clause:

SELECT
    PROJECT,
    SUM_AP,
    SUM_INV
FROM (
    SELECT
        AP1.[PROJECT],
        (SELECT SUM(AP2.Value) FROM AP AS AP2 WHERE AP2.PROJECT = AP1.PROJECT) AS SUM_AP,
        (SELECT SUM(INV2.Value) FROM INV AS INV2 WHERE INV2.PROJECT = AP1.PROJECT) AS SUM_INV
    FROM AP AS AP1
    INNER JOIN INV AS INV1 ON
        AP1.[PROJECT] = INV1.[PROJECT]
    WHERE
        AP1.[PROJECT] = 'XXXXX'
    GROUP BY
        AP1.[PROJECT]
) SQ
WHERE
    SQ.SUM_AP > 1500

Use of the HAVING clause when using muliple sums

2 Answers2