Select SUMmed value from result, > than preset value (Part 2)

[karlomutschler]

Hi,

the following query listed all the contract_id numbers whose revenue amount for a period of 3 months exceeded 250.

SELECT contract_id
FROM contract_turnover
WHERE revenue_date BETWEEN '01.04.2001' AND '01.06.2001'
GROUP BY contract_id, revenue_amount
HAVING SUM( revenue_amount) > 250
;

However, now I wish to know how many contracts there actually were, that exceeded the required amount.

TIA for your help
Kind regards.
Karlo

 

[fluteplr]

SELECT contract_id, count(*)
FROM contract_turnover
WHERE revenue_date BETWEEN '01.04.2001' AND '01.06.2001'
GROUP BY contract_id, revenue_amount
HAVING SUM( revenue_amount) > 250

you can also add
sum(revenue_amount) if you want to know the actual amount.

 

[karlomutschler]

Thanks fluteplr,
for your response and assitance

However, the query-result is not what was expected.
I wish to know how many contracts there actually were, that exceeded the stated amount.
The result should be one line and the figure 3448 (thats how many rows there were initially)

Kind regards
Karlo

 

[carp]

SELECT count(*)
FROM
(SELECT contract_id
FROM contract_turnover
WHERE revenue_date BETWEEN '01.04.2001' AND '01.06.2001'
GROUP BY contract_id
HAVING SUM( revenue_amount) > 250);

This works in Oracle - I don't know about other RDBMS (although I don't think it works in MySQL). Which RDBMS/version are you using?