CODEWITHSUNDEEP
sqloracle-databaseselectcounthaving
Olivier Boulard
Olivier Boulard

Reputation: 3

SQL count(*) with having

I have this query on oracle.

SELECT CBG.refs, CBG.cuo, CBG.date, CBG.nber, CG.date, CBG.conso, 
                (SELECT COUNT(*) 
                    FROM MAD.VIN CBV
                    WHERE CBV.CUO = CBG.CUO AND 
                        CBV.NBER = CBG.NBER AND
                        CBV.DATE = CBG.DATE AND
                        CBV.REFS = CBG.REFS 
                        GROUP BY CUO , DATE , NBER , REFS )  AS COUNTS ,
                CBG.CONSO_CONCESS AS CONCESS
                FROM MAD.GEN CBG, MAD.CAR_GEN CG
                WHERE CBG.cuo = CG.cuo AND 
                CBG.CONSO_DATE IS NOT NULL AND
                CBG.date = CG.date AND 
                CBG.nber = CG.nber
                HAVING COUNTS > 0;

when i run this sql query it gives me an error that says: invalid identifier counts.

How do we get results only if count is greater than a given parameter?

Thanks.

Upvotes: 0

Views: 198

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521502

Unlike in MySQL, in Oracle we cannot refer to an alias in the HAVING clause (aliases can only be referenced in the ORDER BY clause). One workaround would be to put your current logic into a CTE and then filter it.

WITH cte AS (
     SELECT CBG.refs, CBG.cuo, CBG.date AS cbg_date, CBG.nber, CG.date AS cg_date,
            CBG.conso,
            (SELECT COUNT(*) 
             FROM MAD.VIN CBV
             WHERE CBV.CUO = CBG.CUO AND 
                   CBV.NBER = CBG.NBER AND
                   CBV.DATE = CBG.DATE AND
                   CBV.REFS = CBG.REFS 
            GROUP BY CUO, DATE, NBER, REFS) AS COUNTS,
            CBG.CONSO_CONCESS AS CONCESS
    FROM MAD.GEN CBG
    INNER JOIN MAD.CAR_GEN CG
        ON CBG.cuo = CG.cuo AND 
           CBG.date = CG.date AND 
           CBG.nber = CG.nber
    WHERE CBG.CONSO_DATE IS NOT NULL
)

SELECT refs, cuo, cbg_date, nber, cg_date, conso, COUNTS, CONCESS
FROM cte
WHERE COUNTS > 0;

Upvotes: 3

Related Questions