本文介绍了按销售人员和月份分组的SQL查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 大家好,我需要为报告创建一个数据集,其中包含按销售人员分组的月度销售额,然后按月计算给定年份,我正在努力解决的部分是在一些或所有月份可能没有销售但是我需要为这些案例显示零 - 作为起点我有这个(日历表包含未来五十年的日期) SELECT ov.Owner_Id,MONTH(DateIndex) as MonthNumber FROM CalendarTable ct cross join Opp_View ov 其中年(ct.dateindex)= 2016 GROUP BY ov.Owner_Id,MONTH(DateIndex) order by ov.Owner_Id,MonthNum ber 返回 Owner_Id MonthNumber REP1 1 REP1 2 REP1 3 REP1 4 REP1 5 REP1 6 REP1 7 REP1 8 REP1 9 REP1 10 REP1 11 REP1 12 etc ... 所有代表 这就是我想要的,当我按月使用另一个表包括总计金额时,问题就开始了,因为 SELECT ov.Owner_Id,MONTH(DateIndex) as MonthNumber, sum(sf.o_sum_financed) as SumFinanced FROM CalendarTable ct cross join Opp_View ov left outer join O_Sum_Financed sf on ov.Opp_Id = sf.Client_Id 其中年(ct.dateindex)= 2016 GROUP BY ov.Owner_Id,MONTH(DateIndex) order by ov.Owner_Id,MonthNumber 返回这个 Owner_Id MonthNumber SumFinan ced REP1 1 113358054 。 33 REP1 2 106044631 。 47 REP1 3 113358054 。 33 REP1 4 109701342 。 9 REP1 5 113358054 。 33 REP1 6 109701342 。 9 REP1 7 113358054 。 33 REP1 8 113358054 。 33 REP1 9 109701342 。 9 REP1 10 113358054 。 33 REP1 11 109701342 。 9 REP1 12 113358054 。 33 这是完全错误的(例如第一行应该显示420,000)任何想法的章节? 我尝试过什么: SELECT ov.Owner_Id,MONTH(DateIndex)为MonthNumber, sum(sf.o_sum_financed)为SumFinanced FROM CalendarTable ct 交叉加入Opp_View ov left outer加入O_Sum_Financed sf on ov.Opp_Id = sf.Client_Id 其中年份(ct.dateindex)= 2016 GROUP BY ov.Owner_Id, MONTH(DateIndex) 由ov.Owner_Id订购,MonthNumber 解决方案 试试这个: SELECT sq。*,SUM(sf.o_sum_financed) As SumFinanced FROM ( - 您的查询没有经济金额! ) AS sq LEFT OUTER JOIN O_Sum_Financed sf ON sq。 Owner_Id = sf.ClientId GROUP BY sq.Owner_Id,sq.MonthNumber ORDER BY sq.Owner_Id,sq.MonthNumber 只是为了更新 - 我把它搞砸了 - 这个有效 SELECT sq.Owner_Id,sq.MonthNumber,SUM(sf .o_sum_financed)作为 SumFinanced FROM ( SELECT ov.Owner_Id,mths.MonthNumber,ov.Opp_Id FROM 月份mths cross join Opp_View ov where year(ov.close_date )= 2016 和 mths.MonthNumber = month(ov.close_date)或月(ov.close_date) null ) AS sq LEFT OUTER JOIN O_Sum_Financed sf ON sq.Opp_Id = sf.Client_Id GROUP BY sq.Owner_Id, sq.MonthNumber ORDER BY sq.Owner_Id,sq.MonthNumber Hi all, I need to create a dataset for a report containg monthly sales grouped by salesperson and then by month for a given year, the part I'm struggling with is for some or all months there might not be any sales but I need to show zeroes for these cases - as a starting point I have this ( the calendar table contains dates for the next fifty years) SELECT ov.Owner_Id,MONTH(DateIndex) as MonthNumber FROM CalendarTable ct cross join Opp_View ov where year(ct.dateindex) = 2016 GROUP BY ov.Owner_Id,MONTH(DateIndex) order by ov.Owner_Id,MonthNumberwhich returns Owner_Id MonthNumberREP1 1REP1 2REP1 3REP1 4REP1 5REP1 6REP1 7REP1 8REP1 9REP1 10REP1 11REP1 12etc... for all representativeswhich is what I want, the problem starts when I include summed amounts by month using another table as in SELECT ov.Owner_Id,MONTH(DateIndex) as MonthNumber,sum(sf.o_sum_financed) as SumFinanced FROM CalendarTable ct cross join Opp_View ov left outer join O_Sum_Financed sf on ov.Opp_Id = sf.Client_Id where year(ct.dateindex) = 2016 GROUP BY ov.Owner_Id,MONTH(DateIndex) order by ov.Owner_Id,MonthNumberwhich returns thisOwner_IdMonthNumberSumFinancedREP1 1113358054.33REP1 2106044631.47REP1 3113358054.33REP1 4109701342.9REP1 5113358054.33REP1 6109701342.9REP1 7113358054.33REP1 8113358054.33REP1 9109701342.9REP1 10113358054.33REP1 11109701342.9REP1 12113358054.33which is totally wrong ( e.g the first row should show 420,000 ) any ideas chaps ?What I have tried:SELECT ov.Owner_Id,MONTH(DateIndex) as MonthNumber,sum(sf.o_sum_financed) as SumFinanced FROM CalendarTable ct cross join Opp_View ov left outer join O_Sum_Financed sf on ov.Opp_Id = sf.Client_Id where year(ct.dateindex) = 2016 GROUP BY ov.Owner_Id,MONTH(DateIndex) order by ov.Owner_Id,MonthNumber 解决方案 Try this:SELECT sq.*, SUM(sf.o_sum_financed) As SumFinancedFROM ( -- your query without financial sum here! ) AS sq LEFT OUTER JOIN O_Sum_Financed sf ON sq.Owner_Id = sf.ClientIdGROUP BY sq.Owner_Id, sq.MonthNumberORDER BY sq.Owner_Id, sq.MonthNumberJust to update - I sussed it out - this worksSELECT sq.Owner_Id, sq.MonthNumber, SUM(sf.o_sum_financed) As SumFinancedFROM (SELECT ov.Owner_Id,mths.MonthNumber,ov.Opp_IdFROM Months mthscross join Opp_View ovwhere year(ov.close_date) = 2016and mths.MonthNumber = month(ov.close_date) or month(ov.close_date) is null) AS sq LEFT OUTER JOIN O_Sum_Financed sf ON sq.Opp_Id = sf.Client_IdGROUP BY sq.Owner_Id, sq.MonthNumberORDER BY sq.Owner_Id, sq.MonthNumber 这篇关于按销售人员和月份分组的SQL查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!
09-15 23:57