Summarize the total by category

General
MMM
Enthusiast

Summarize the total by category

I get the full data for each category instead of getting the full total for each category. For example the output came out 9936 instead of 5 rows with totals

SELECT

 SV.CATEGORY_DSC,

 SUM(SV.BILL_UNIT_CNT) BILL_UNIT_CNT,

 SUM(SV.BILL_DAY_CNT) BILL_DAY_CNT,

 SUM(SV.BILL_AMT)BILL_AMT

 FROM EDWMTH.CL_BILL_MTL SV,

     EDWMTH.CL_SUMM_MTL SM

WHERE SV.PAID_IND = 'Y' AND SV.CATEGORY_DSC <>'Unknown'

      AND SV.CL_JOIN_KEY = SM.CL_JOIN_KEY AND SM.CL_PAID_IND = 'Y'

   AND SV.BILL_BEG_DT >= '2009-01-01' AND SV.BILL_BEG_DT <= '2009-01-31'

GROUP BY

 SV.CATEGORY_DSC,

 SV.BILL_UNIT_CNT,

 SV.BILL_DAY_CNT,

 SV.BILL_AMT

ORDER BY 1

 ;

1 REPLY
Enthusiast

Re: Summarize the total by category

You are not supposed to GROUP BY on all the columns of your select when you are doing SUM. Just do GROUP BY on the first column and you should get all the metrics at the category level.