sqloracle

Oracle SQL how to get the group by columns along with other selected columns


I have the below query :

SELECT COUNT(CASE WHEN T.TENANCY_STATUS = 'ACTIVE' THEN 1 END) TotalCount,
       COUNT(CASE WHEN T.UNIT_TYPE_DESCR_E  = 'APARTMENT' THEN 1 END) ApartmentCountTotal,
       COUNT(CASE WHEN T.UNIT_TYPE_DESCR_E  = 'VILLA' THEN 1 END) VillaCountTotal,
       COUNT(CASE WHEN T.UNIT_TYPE_DESCR_E  = 'STUDIO' THEN 1 END) StudioCountTotal,
       COUNT(CASE WHEN T.UNIT_TYPE_DESCR_E  = 'DUPLEX' THEN 1 END) DuplexCountTotal,
       COUNT(CASE WHEN T.UNIT_TYPE_DESCR_E  = 'APARTMENT' AND T.TENANCY_STATUS = 'ACTIVE' THEN 1 END) ApartmentCountActiveTotal,
       COUNT(CASE WHEN T.UNIT_TYPE_DESCR_E  = 'VILLA' AND T.TENANCY_STATUS = 'ACTIVE' THEN 1 END) VillaCountActiveTotal,
       COUNT(CASE WHEN T.UNIT_TYPE_DESCR_E  = 'STUDIO' AND T.TENANCY_STATUS = 'ACTIVE' THEN 1 END) StudioCountActiveTotal,
       COUNT(CASE WHEN T.UNIT_TYPE_DESCR_E  = 'DUPLEX' AND T.TENANCY_STATUS = 'ACTIVE' THEN 1 END) DuplexCountActiveTotal
FROM CMT WHERE COND1 = COND1 AND COND2=COND2

Now , along with the above select values in the query I wanted the below results to also come

(SELECT ROUND (SUM(RENTAL_VALUE_PER_UNIT)*0.05) ApartmentMuncipalFee FROM CMT T WHERE COND1 = COND1 
       AND T.unit_usage_type_descr_e = 'RESIDENTIAL' 
       AND T.UNIT_TYPE_DESCR_E  = 'APARTMENT'
       GROUP BY T.UNIT_TYPE_DESCR_E)

       (SELECT ROUND (SUM(RENTAL_VALUE_PER_UNIT)*0.05) VillaMuncipalFee FROM CMT T WHERE COND1 = COND1 
       AND T.unit_usage_type_descr_e = 'RESIDENTIAL' 
       AND T.UNIT_TYPE_DESCR_E  = 'VILLA'
       GROUP BY T.UNIT_TYPE_DESCR_E) VillaMuncipalFee

Kindly need help


Solution

  • Add them into SELECT statement :

    SELECT . . . ,
           SUM(CASE WHEN T.unit_usage_type_descr_e = 'RESIDENTIAL' AND T.UNIT_TYPE_DESCR_E  = 'APARTMENT' THEN RENTAL_VALUE_PER_UNIT * 0.05 ELSE 0 END) AS ApartmentMuncipalFee, 
           SUM(CASE WHEN T.unit_usage_type_descr_e = 'RESIDENTIAL' AND T.UNIT_TYPE_DESCR_E  = 'VILLA' THEN RENTAL_VALUE_PER_UNIT * 0.05 ELSE 0 END) AS VillaMuncipalFee
    FROM CMT