Sql query to group by salesperson and month

Question

0.00/5 (No votes)

See more:

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)

SQL

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

SQL

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

SQL

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

SQL

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

Posted 25-May-16 1:31am

pkfox

Updated 25-May-16 21:44pm

v3

Add a Solution

2 solutions

Solution 1

Try this:

SQL

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

Posted 25-May-16 1:50am

Maciej Los

Updated 25-May-16 2:28am

v2

Comments

pkfox 25-May-16 8:23am

I'm probably misunderstanding what you mean I tried this and get syntax errors

SELECT sq.*, SUM(sf.o_sum_financed) As SumFinanced,count(opp_id) as DealCount
FROM (SELECT ov.Owner_Id,MONTH(DateIndex) as MonthNumber,ov.Opp_Id
FROM CalendarTable ct
cross join Opp_View ov
where year(ct.dateindex) = 2016
GROUP BY ov.Owner_Id,MONTH(DateIndex)
) AS sq
LEFT OUTER JOIN O_Sum_Financed sf ON sq.opp_Id = sf.Client_Id

Maciej Los 25-May-16 8:29am

Yes, you did understand. I missed to add GROUP BY clause. Check again.

pkfox 25-May-16 8:47am

Where do I put the group by ?

Maciej Los 25-May-16 8:54am

At the end of statement. See udpated answer.

pkfox 25-May-16 8:55am

This is what I have and still getting syntax error

SELECT sq.*, SUM(sf.o_sum_financed) As SumFinanced
FROM (
SELECT ov.Owner_Id,MONTH(DateIndex) as MonthNumber,ov.Opp_Id
FROM CalendarTable ct
cross join Opp_View ov
where year(ct.dateindex) = 2016
GROUP BY ov.Owner_Id,MONTH(DateIndex)
) 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

Error is: Column 'Opp_View.Opp_Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

but I need opp_id for the join

Maciej Los 25-May-16 9:00am

Replace:
SELECT sq.*, SUM(sf.o_sum_financed) As SumFinanced
with
SELECT sq.Owner_Id, sq.MonthNumber, SUM(sf.o_sum_financed) As SumFinanced

pkfox 25-May-16 9:07am

Thanks for your time but I'm still getting the error

This is what I have

SELECT sq.Owner_Id, sq.MonthNumber, SUM(sf.o_sum_financed) As SumFinanced
FROM (
SELECT ov.Owner_Id,MONTH(DateIndex) as MonthNumber,ov.Opp_Id
FROM CalendarTable ct
cross join Opp_View ov
where year(ct.dateindex) = 2016
GROUP BY ov.Owner_Id,MONTH(DateIndex)
) 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

pkfox 25-May-16 9:44am

This the output for REP1 there is a pattern in the summed column which might help you see the problem

Owner_Id MonthNumber SumFinanced
REP1 1 15664651.85
REP1 2 14654029.15
REP1 3 15664651.85
REP1 4 15159340.5
REP1 5 15664651.85
REP1 6 15159340.5
REP1 7 15664651.85
REP1 8 15664651.85
REP1 9 15159340.5
REP1 10 15664651.85
REP1 11 15159340.5
REP1 12 15664651.85

pkfox 25-May-16 9:22am

This returns the correct amount of rows but still incorrect summed amounts ( I removed the group clause on the inner query )

SELECT sq.Owner_Id, sq.MonthNumber, SUM(sf.o_sum_financed) As SumFinanced
FROM (
SELECT ov.Owner_Id,MONTH(DateIndex) as MonthNumber,ov.Opp_Id
FROM CalendarTable ct
cross join Opp_View ov
where year(ct.dateindex) = 2016
) 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

pkfox 25-May-16 10:08am

Making progress this is the query ( I've substituted the Calendar table with one called Months which has 12 records )

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

and the output is

Owner_Id MonthNumber SumFinanced
BCARR 1 505311.35
BCARR 2 505311.35
BCARR 3 505311.35
BCARR 4 505311.35
BCARR 5 505311.35
BCARR 6 505311.35
BCARR 7 505311.35
BCARR 8 505311.35
BCARR 9 505311.35
BCARR 10 505311.35
BCARR 11 505311.35
BCARR 12 505311.35

where 505311.35.is the total for the year for BCARR - HTH

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

pkfox · Accepted Answer · 2016-05-25T21:44:00

Just to update - I sussed it out - this works

SQL

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 query to group by salesperson and month

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Sql query to group by salesperson and month

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us