Is there any way to use COUNT variable in having clause when direct count is not possible in long multi table query

Question

I have been getting this logical error, I need to get rows that have count data more than 0. I have tried

'HAVING c>0'

but it return as error

'ORA-00904: "C": invalid identifier'

Following is the query i have been trying to give me results.

SELECT ACCOUNTS.ACCOUNT_NO,(Select count(*) as cc from SALE where sale.account_no=accounts.account_no and DATED >= add_months(sysdate, -6) ) as c, (select LISTAGG(v.TYPE_NAME, ',') WITHIN GROUP (ORDER BY v.TYPE_NAME) AS deals  from  DEALSIN v  where account_no=accounts.account_no ) as deals FROM ACCOUNTS,DETAIL_ACCOUNTS,CITY WHERE ACCOUNTS.ACCOUNT_NO = DETAIL_ACCOUNTS.ACCOUNT_NO AND DETAIL_ACCOUNTS.CITY = REGISTRATION_NO AND PARENT_CODE = 111 AND STATUS = 'A' AND CITY =:CITY_Name having c>0 order by c desc 

I want output as

ACCOUNT_NO  C   DEALS
1           10  deal1,deal2
2           9   deal1,deal3
3           3   deal4

but what I am getting is

ACCOUNT_NO  C   DEALS
1           10  deal1,deal2
2           9   deal1,deal3
3           3   deal4
4           0   deal  ----> DON'T WANT THIS ROW
5           0   deal0 ----> DON'T WANT THIS ROW

I dont want this row with count=0.

Answer 1

I suggest rephrasing your query to do away with those inlined correlated subqueries in the select clause. Instead, left join to separate subqueries on the SALE and DEALSIN tables.

SELECT
    a.ACCOUNT_NO,
    COALESCE(s.cc, 0) AS c,
    COALESCE(v.deals, 'NA') AS deals,
FROM ACCOUNTS a
LEFT JOIN
(
    SELECT account_no, COUNT(*) AS cc
    FROM SALE
    WHERE DATED >= ADD_MONTHS(SYSDATE, -6)
    GROUP BY account_no
) s
    ON s.account_no = a.account_no
LEFT JOIN
(
    SELECT account_no,
        LISTAGG(v.TYPE_NAME, ',') WITHIN GROUP (ORDER BY v.TYPE_NAME) AS deals
    FROM DEALSIN
    GROUP BY account_no
) v
    ON v.account_no = a.account_no
INNER JOIN DETAIL_ACCOUNTS da
    ON a.ACCOUNT_NO = da.ACCOUNT_NO
INNER JOIN CITY c
    ON da.CITY = c.REGISTRATION_NO
WHERE
    PARENT_CODE = 111 AND
    STATUS = 'A' AND
    CITY =:CITY_Name AND
    COALESCE(s.cc, 0) > 0   -- your previous HAVING clause now appears in WHERE
ORDER BY
    c DESC;