CODEWITHSUNDEEP
mysqlsqlcount
blarg
blarg

Reputation: 3893

Multiple aggregate functions in HAVING clause

Due to the nature of my query i have records with counts of 3 that would also fit the criteria of having count of 2 and so on. I was wondering is it possible to query 'having count more than x and less than 7' ? How could I write this. Here is my current code.

GROUP BY meetingID
HAVING COUNT( caseID )<4

I'd like something like

GROUP BY meetingID
HAVING COUNT( caseID )<4 AND >2

That way it would only count for exactly 3

Upvotes: 54

Views: 206288

Answers (6)

Mukesh Kumar
Mukesh Kumar

Reputation: 2376

Here I am writing full query which will clear your all doubts

SELECT BillingDate,
       COUNT(*) AS BillingQty,
       SUM(BillingTotal) AS BillingSum
FROM Billings
WHERE BillingDate BETWEEN '2002-05-01' AND '2002-05-31'
GROUP BY BillingDate
HAVING COUNT(*) > 1
AND SUM(BillingTotal) > 100
ORDER BY BillingDate DESC

Upvotes: 3

Mamun
Mamun

Reputation: 1

select CUSTOMER_CODE,nvl(sum(decode(TRANSACTION_TYPE,'D',AMOUNT)),0)) DEBIT,nvl(sum(DECODE(TRANSACTION_TYPE,'C',AMOUNT)),0)) CREDIT,
nvl(sum(decode(TRANSACTION_TYPE,'D',AMOUNT)),0)) - nvl(sum(DECODE(TRANSACTION_TYPE,'C',AMOUNT)),0)) BALANCE from TRANSACTION   
GROUP BY CUSTOMER_CODE
having nvl(sum(decode(TRANSACTION_TYPE,'D',AMOUNT)),0)) > 0
AND (nvl(sum(decode(TRANSACTION_TYPE,'D',AMOUNT)),0)) - nvl(sum(DECODE(TRANSACTION_TYPE,'C',AMOUNT)),0))) > 0

Upvotes: -1

Taryn
Taryn

Reputation: 247820

There is no need to do two checks, why not just check for count = 3:

GROUP BY meetingID
HAVING COUNT(caseID) = 3

If you want to use the multiple checks, then you can use:

GROUP BY meetingID
HAVING COUNT(caseID) > 2
 AND COUNT(caseID) < 4

Upvotes: 11

Achrome
Achrome

Reputation: 7821

Something like this?

HAVING COUNT(caseID) > 2
AND COUNT(caseID) < 4

Upvotes: 2

lc.
lc.

Reputation: 116528

For your example query, the only possible value greater than 2 and less than 4 is 3, so we simplify:

GROUP BY meetingID
HAVING COUNT(caseID) = 3

In your general case:

GROUP BY meetingID
HAVING COUNT(caseID) > x AND COUNT(caseID) < 7

Or (possibly easier to read?),

GROUP BY meetingID
HAVING COUNT(caseID) BETWEEN x+1 AND 6

Upvotes: 5

Trent Earl
Trent Earl

Reputation: 3607

GROUP BY meetingID
HAVING COUNT(caseID) < 4 AND COUNT(caseID) > 2

Upvotes: 114

Related Questions