Multiple aggregate functions in HAVING clause

Question

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   lt 4   I d like something like  GROUP BY meetingID HAVING COUNT  caseID   lt 4 AND  gt 2   That way it would only count for exactly 3

User · Answer

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    gt  0 AND  nvl sum decode TRANSACTION TYPE  D  AMOUNT   0   - nvl sum DECODE TRANSACTION TYPE  C  AMOUNT   0     gt  0

User · Answer

GROUP BY meetingID HAVING COUNT caseID   lt  4 AND COUNT caseID   gt  2

User · Answer

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     gt  1 AND SUM BillingTotal   gt  100 ORDER BY BillingDate DESC

User · Answer

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   gt  x AND COUNT caseID   lt  7   Or  possibly easier to read     GROUP BY meetingID HAVING COUNT caseID  BETWEEN x 1 AND 6

User · Answer

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   gt  2  AND COUNT caseID   lt  4

User · Answer

Something like this   HAVING COUNT caseID   gt  2 AND COUNT caseID   lt  4

[mysql] Multiple aggregate functions in HAVING clause

Examples related to mysql

Examples related to sql

Examples related to count