How to get the count of a query with Group by Clause in Oracle

Hi, This post shows you how to get the count of the query where group by clause is used in Oracle.

The below query is used to fetch sales order related data. The inner query contains the GROUP BY Clause and the outer query is used on top of it to get the count of the main query.

SELECT COUNT(*)  FROM 

(SELECT HCA.ACCOUNT_NUMBER "ACCOUNT_NUMBER"

      ,HP.PARTY_NAME "PARTY_NAME" 

      ,OOS.NAME "ORDER_SOURCE_NAME"

      ,COUNT(OOH.ORDER_NUMBER) "ORDER_COUNT"

 FROM OE_ORDER_HEADERS_ALL OOH,

      HZ_CUST_ACCOUNTS_ALL HCA,

      HZ_PARTIES HP,

      OE_ORDER_SOURCES OOS

WHERE OOH.ORDER_SOURCE_ID IN(1001, 13)             --custom order source IDs

  AND OOH.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID

  AND HCA.PARTY_ID = HP.PARTY_ID

  AND TRUNC(OOH.CREATION_DATE) BETWEEN '01-JAN-2018' AND '31-JAN-2018'

  AND OOS.ORDER_SOURCE_ID = OOH.ORDER_SOURCE_ID

GROUP BY OOS.NAME,

HCA.ACCOUNT_NUMBER

        ,HP.PARTY_NAME

ORDER BY 3 DESC);

Any comments or queries? Hit them in the comment section below. Happy learning!!