按销售人员和月份分组的SQL查询 [英] Sql query to group by salesperson and month

问题描述

大家好，我需要为报告创建一个数据集，其中包含按销售人员分组的月度销售额，然后按月计算给定年份，我正在努力解决的部分是在一些或所有月份可能没有销售但是我需要为这些案例显示零 - 作为起点我有这个（日历表包含未来五十年的日期）

  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 < span class =code-digit> 1  
 REP1  2  
 REP1  3  
 REP1  4  
 REP1  5  
 REP1 < span class =code-digit> 6  
 REP1  7  
 REP1  8  
 REP1  9  
 REP1  10  
 REP1 < span class =code-digit> 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 
 < span class =code-keyword>其中年（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,MonthNumber

which returns

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... for all representatives

which 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,MonthNumber

which returns this

Owner_Id	MonthNumber	SumFinanced
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

which 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 SumFinanced
FROM (
    -- your query without financial sum here!
    ) 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

Just to update - I sussed it out - this works

SELECT sq.Owner_Id, sq.MonthNumber, SUM(sf.o_sum_financed) As SumFinanced
FROM (
SELECT ov.Owner_Id,mths.MonthNumber,ov.Opp_Id
FROM  Months mths
cross join Opp_View ov
where year(ov.close_date) = 2016
and 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_Id
GROUP BY sq.Owner_Id, sq.MonthNumber
ORDER BY sq.Owner_Id, sq.MonthNumber

这篇关于按销售人员和月份分组的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！