asked
Returning An Average Grouping Result Not Based On All Columns Grouped
Hi, I've got a table that contains company sales by salesman which details if the deal was cash or finance, and the sale interest rate if that sale was a finance deal. I'm trying to write a query to pull back the average finance deal rate grouped by the salesman if the the deals a finance deal. Using the following example data: DROP TABLE RPT_SALES_BY_SALESMAN CREATE TABLE RPT_SALES_BY_SALESMAN( SALESMAN VARCHAR(100), COMPANY VARCHAR(100), FINANCE_DEAL CHAR(1), SALE_INTEREST_RATE NUMERIC(5,2)) INSERT INTO RPT_SALES_BY_SALESMAN(SALESMAN,COMPANY,FINANCE_DEAL,SALE_INTEREST_RATE) VALUES('ABC','XYZ PLC','1',7) INSERT INTO RPT_SALES_BY_SALESMAN(SALESMAN,COMPANY,FINANCE_DEAL,SALE_INTEREST_RATE) VALUES('ABC','UVW LTD','1',7) INSERT INTO RPT_SALES_BY_SALESMAN(SALESMAN,COMPANY,FINANCE_DEAL,SALE_INTEREST_RATE) VALUES('ABC','XYZ PLC','1',7) INSERT INTO RPT_SALES_BY_SALESMAN(SALESMAN,COMPANY,FINANCE_DEAL,SALE_INTEREST_RATE) VALUES('ABC','UVW LTD','1',7) INSERT INTO RPT_SALES_BY_SALESMAN(SALESMAN,COMPANY,FINANCE_DEAL,SALE_INTEREST_RATE) VALUES('ABC','RST LTD','0',0) INSERT INTO RPT_SALES_BY_SALESMAN(SALESMAN,COMPANY,FINANCE_DEAL,SALE_INTEREST_RATE) VALUES('DEF','OPQ PLC','0',0) INSERT INTO RPT_SALES_BY_SALESMAN(SALESMAN,COMPANY,FINANCE_DEAL,SALE_INTEREST_RATE) VALUES('DEF','LMN PLC','1',7) INSERT INTO RPT_SALES_BY_SALESMAN(SALESMAN,COMPANY,FINANCE_DEAL,SALE_INTEREST_RATE) VALUES('DEF','UVW LTD','1',7) INSERT INTO RPT_SALES_BY_SALESMAN(SALESMAN,COMPANY,FINANCE_DEAL,SALE_INTEREST_RATE) VALUES('GHI','IJK LTD','1',7) I need to pull back a query in the following results: SALESMAN COMPANY FINANCE_DEAL SALE_INTEREST_RATE AVERAGE_FINANCE_DEAL_RATE ABC XYZ PLC 1 7 5.600000 ABC UVW LTD 1 7 5.600000 ABC XYZ PLC 1 7 5.600000 ABC UVW LTD 1 7 5.600000 ABC RST LTD 0 0 0.0 DEF OPQ PLC 0 0 0.0 DEF LMN PLC 1 7 4.666666 DEF UVW LTD 1 7 4.666666 GHI IJK LTD 1 7 7.000000 If I do the usual grouping by SALESMAN I just get an average per SALESMAN: SELECT SALESMAN, AVG(SALE_INTEREST_RATE) AS AVERAGE_FINANCE_DEAL_RATE FROM RPT_SALES_BY_SALESMAN GROUP BY SALESMAN SALESMAN AVERAGE_FINANCE_DEAL_RATE ABC 5.600000 DEF 4.666666 GHI 7.000000 If I add all columns that have a FINANCE_DEAL the grouping is still incorrect: SELECT SALESMAN,COMPANY,FINANCE_DEAL,SALE_INTEREST_RATE, AVG(SALE_INTEREST_RATE) AS AVERAGE_FINANCE_DEAL_RATE FROM RPT_SALES_BY_SALESMAN WHERE FINANCE_DEAL = 1 GROUP BY SALESMAN,COMPANY,FINANCE_DEAL,SALE_INTEREST_RATE SALESMAN COMPANY FINANCE_DEAL SALE_INTEREST_RATE AVERAGE_FINANCE_DEAL_RATE ABC UVW LTD 1 7.00 7.000000 ABC XYZ PLC 1 7.00 7.000000 DEF LMN PLC 1 7.00 7.000000 DEF UVW LTD 1 7.00 7.000000 GHI IJK LTD 1 7.00 7.000000 I need the average calculation to be done on the fly in the query. Any ideas? TIA EDIT: Sorry I meant to say that this is for a SS2000 legacy database. Apologies.
You can get the average per salesman by using a aggregate windowed function select *, avg(SALE_INTEREST_RATE)over(partition by SALESMAN) as AVERAGE_FINANCE_DEAL_RATE from RPT_SALES_BY_SALESMAN Then for the 2 rows where SALE_INTEREST_RATE is 0, simply use that: select *, case when FINANCE_DEAL = 1 then avg(SALE_INTEREST_RATE)over(partition by SALESMAN) when FINANCE_DEAL = 0 then 0 end as AVERAGE_FINANCE_DEAL_RATE from RPT_SALES_BY_SALESMAN or if you are unfortunate to be using SQL2000 :) select RPT_SALES_BY_SALESMAN.SALESMAN , RPT_SALES_BY_SALESMAN.COMPANY , RPT_SALES_BY_SALESMAN.FINANCE_DEAL , RPT_SALES_BY_SALESMAN.SALE_INTEREST_RATE, case when FINANCE_DEAL = 1 then aggregated.AVG_SALE_INTEREST_RATE when FINANCE_DEAL = 0 then 0 end as AVERAGE_FINANCE_DEAL_RATE from RPT_SALES_BY_SALESMAN join ( select SALESMAN, avg(SALE_INTEREST_RATE) AVG_SALE_INTEREST_RATE from dbo.RPT_SALES_BY_SALESMAN group by SALESMAN ) aggregated on aggregated.SALESMAN = dbo.RPT_SALES_BY_SALESMAN.SALESMAN
Ouch! Added another version that (I think - haven't got a 2000 instance available to test on) works for 2000
14 People are following this question.
