Hi there,

I have a SQL query which I need to add in group by clause, but I have subqueries which I do not want
to group. But technically, I must include all the columns in the select to the group by clause. So, I wish to know what
is the simplest method to do it without group in the subqueries.

Here's my code (without group by):-

Code:
SELECT 
T_LC_LICENSE_APPL.APPLICATION_NO AS APPLICATION_NO, 
T_LC_LICENSE_APPL.REMARKS AS REMARKS, 
T_LC_LICENSE_APPL.USER_OBJECTID AS USER_OBJECTID, 
T_LC_LICENSE_APPL_TYPE.OBJECTID AS APPL_TYPE_OBJID, 
T_LC_TYPE.LICENSE_TYPE_BM AS LIC_TYPE, 
T_LC_LICENSE_APPL.CREATED_DATE AS APPLICATION_DATE,
CASE WHEN T_LC_LICENSE_APPL.NAME = '' OR T_LC_LICENSE_APPL.NAME IS NULL THEN T_LC_USER.NAME ELSE T_LC_LICENSE_APPL.NAME END AS APPLICANT_NAME, 
NVL(T_LC_LICENSE_APPL.COR_ADDRESS1,'') AS GUEST_ADDR1, 
NVL(T_LC_LICENSE_APPL.COR_ADDRESS2,'') AS GUEST_ADDR2, 
NVL(T_LC_LICENSE_APPL.COR_ADDRESS3,'') AS GUEST_ADDR3, 
T_LC_LICENSE_APPL.COR_POSTCODE AS GUEST_POSTCODE, 
NVL(T_LC_USER.COR_ADDRESS2,'') AS REG_USER_ADDR2, 
NVL(T_LC_USER.COR_ADDRESS3,'') AS REG_USER_ADDR3, 
T_LC_USER.COR_POSTCODE AS REG_USER_POSTCODE, 
(SELECT DESCRIPTION_BM FROM T_REF_COUNTRY WHERE OBJECTID = T_LC_LICENSE_APPL.COR_COUNTRY_OBJECTID) AS GUEST_COUNTRY, 
(SELECT DESCRIPTION_BM FROM T_REF_CITY WHERE OBJECTID = T_LC_LICENSE_APPL.COR_CITY_OBJECTID) AS GUEST_CITY, 
(SELECT DESCRIPTION_BM FROM T_REF_STATE WHERE OBJECTID = T_LC_LICENSE_APPL.COR_STATE_OBJECTID) AS GUEST_STATE, NVL(T_LC_USER.COR_ADDRESS1,'') AS REG_USER_ADDR1, 
(SELECT DESCRIPTION_BM FROM T_REF_COUNTRY WHERE OBJECTID = T_LC_USER.COR_COUNTRY_OBJECTID) AS REG_USER_COUNTRY, 
(SELECT DESCRIPTION_BM FROM T_REF_CITY WHERE OBJECTID = T_LC_USER.COR_CITY_OBJECTID) AS REG_USER_CITY, 
(SELECT DESCRIPTION_BM FROM T_REF_STATE WHERE OBJECTID = T_LC_USER.COR_STATE_OBJECTID) AS REG_USER_STATE, 
NVL((SELECT DISTINCT(T_USER_DEPARTMENT.CODE) FROM T_USER_DEPARTMENT, T_LC_COMMENTER, T_LC_CATEGORY WHERE T_LC_COMMENTER.USER_DEPARTMENT_OBJECTID = T_USER_DEPARTMENT.OBJECTID AND T_USER_DEPARTMENT.CODE IN('JTK', 'PB')),'') AS CATE_DEPART, 
NVL((SELECT T_LC_ENDORSE.CODE FROM T_LC_ENDORSE, T_LC_LICENSE_APPL_COMMENT, T_USER_DEPARTMENT WHERE T_LC_LICENSE_APPL_COMMENT.LICENSE_APPL_TYPE_OBJECTID =  T_LC_LICENSE_APPL_TYPE.OBJECTID AND T_LC_ENDORSE.OBJECTID = T_LC_LICENSE_APPL_COMMENT.STATUS AND T_LC_LICENSE_APPL_COMMENT.DEPT_ID = T_USER_DEPARTMENT.OBJECTID AND T_USER_DEPARTMENT.CODE IN('JTK', 'PB')), 'TB') AS COMM_STAT, 
(SELECT T_LC_LICENSE_APPL_COMMENT.REMARK FROM T_LC_LICENSE_APPL_COMMENT, T_USER_DEPARTMENT WHERE T_LC_LICENSE_APPL_COMMENT.LICENSE_APPL_TYPE_OBJECTID =  T_LC_LICENSE_APPL_TYPE.OBJECTID AND T_LC_LICENSE_APPL_COMMENT.DEPT_ID = T_USER_DEPARTMENT.OBJECTID AND T_USER_DEPARTMENT.CODE IN('JTK','PB')) AS COMM_REMARK

FROM 
T_LC_LICENSE_APPL LEFT JOIN T_LC_USER ON (T_LC_LICENSE_APPL.USER_OBJECTID = T_LC_USER.OBJECTID), T_LC_LICENSE_APPL_TYPE, T_LC_TYPE 

WHERE 
T_LC_LICENSE_APPL.OBJECTID = T_LC_LICENSE_APPL_TYPE.LICENSE_APPL_OBJECTID AND T_LC_TYPE.OBJECTID = T_LC_LICENSE_APPL_TYPE.TYPE_OBJECTID AND T_LC_LICENSE_APPL.OBJECTID IN ('013EC99C032B6A1B','013EC152365726A6') 

ORDER BY 
T_LC_LICENSE_APPL.APPLICATION_NO, T_LC_TYPE.CODE

Please advise.

Thanks