All Forums General
MMM 6 posts Joined 04/13
10 Apr 2013
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
 ;
 

KS42982 137 posts Joined 12/12
11 Apr 2013

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.

You must sign in to leave a comment.