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!!

Script to run SQL Query from Host file in Oracle apps

Hi, This post shows you the sample script to run SQL query from host file in Oracle Apps.

In the below example, I used a query to fetch the directory path.

#!/bin/ksh

#----- Standard Parameters: -----#

p_apps_passwd=`echo ${1}`

p_user_id=`echo ${2}`

p_username=`echo ${3}`

p_request_id=`echo ${4}`

target_path=/u01/apps/apps_st/appl/jtm/12.0.0

p_file_directory()                                                         #Directory Path

{

sqlplus -s ${p_apps_passwd} << EOF                        #Starts from here

WHENEVER SQLERROR EXIT SQL.SQLCODE;

SET LINES 255

SET PAGES 0

SET FEEDBACK OFF

SET ECHO OFF

SET PAGESIZE 0

SET TIMING OFF

SET SERVEROUTPUT ON

SET TERM ON

SELECT file_path FROM request_table;                    #sample query

EOF

}

echo "Directory Path: $p_file_directory"                    #Printing directory path

# - Represents comments

Hope this helps for beginners. Great learning ahead!!