I have the following SQLSELECT Scheme, Application_Date, LTVGroup, LTVSort, SUM(LoanGrossAmount) AS ApplicationValues, COUNT(ApplicationRef) AS ApplicationCount, Rate, ProductCode,ApplicationRefFROM reporting..AppsInLTVGROUP BY Scheme, Application_Date, LTVGroup, LTVSort, Rate, ProductCode,ApplicationRef
which produces the following for appref = XXXXXXXXSC,2006-10-19 00:00:00.000,80.01%-85%,4,501247.00,1,5.49%,SC3,XXXXXXXXSC,2006-10-19 00:00:00.000,80.01%-85%,4,501247.00,1,5.99%,SC5,XXXXXXXX
Because application 'XXXXXXXX'is split over 2 products there are 2 rows returned which is fine but I would like the Application count field to reflect this by dividing the result by the number of times the app number appears (eg for this example 2 but it could be 1,2,3 or 4 products) is there any way of doing this so I can get the result to be SC,2006-10-19 00:00:00.000,80.01%-85%,4,501247.00,0.5,5.49%,SC3,XXXXXXXXSC,2006-10-19 00:00:00.000,80.01%-85%,4,501247.00,0.5,5.99%,SC5,XXXXXXXX
so if it appear once divide by 1, if it apppears twice divide by 2, ..., if it appears 4 times then divide by 4.Hope anyone can help.
Problem with count result in a view
Posted - 2006-10-20 : 09:41:16
