• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 661
  • Last Modified:

SQL for monthly sales figures

I have two tables in Mysql, one with sales data and the other with salesmen:
Sales Table is Fin_data: deposit_date, closer_id, inv_amount
Salesmen Table users: ID, full_name.
The closer_id links to the ID in the users table.

The user can select a date range e.g. 2009-01-01 to 2009-04-30. All this is working correctly.

The result I want is total sales by salesman for each of the months in the date range and total for the month and grand total for all the months in the range selected.
I would think that this could be done via sql. Hope I am right!

0
theseowgroup
Asked:
theseowgroup
  • 6
  • 4
  • 4
1 Solution
 
Nathan RileyFounder/CTOCommented:

select S.full_name, sum(inv_amount), 'Month Total' = (select sum(inv_amount) from Sales where deposit_date >= cast('04/01/2009' as datetime)
and deposit_date <= cast('04/30/2009' as datetime))
from Salesmen S
inner join Sales SA on S.closer_ID = S.ID
where deposit_date >= cast('04/01/2009' as datetime)
and deposit_date <= cast('04/30/2009' as datetime)
group by S.full_name

Open in new window

0
 
theseowgroupAuthor Commented:
This is what I end up with:

select S.full_name, sum(inv_amount), 'Month Total' = (select sum(inv_amount) from fin_data where deposit_date >= '2009-01-01'
and deposit_date <= '2009-04-30')
from users S
inner join fin_data SA on SA.closer_ID = S.ID
where deposit_date >= '2009-01-01'
and deposit_date <= '2009-04-01'
group by S.full_name

No Month Total is returned and the sales value for each of the salesmen is the total for the period, not by each month.
0
 
Nathan RileyFounder/CTOCommented:
Can the Month total be a separate query?  Try this for the Total per month per salesperson
select S.full_name, sum(inv_amount)
from users S
inner join fin_data SA on SA.closer_ID = S.ID
where deposit_date >= '2009-01-01'
and deposit_date <= '2009-04-01'
group by S.full_name, month(deposit_date)

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
theseowgroupAuthor Commented:
This is as far as I have got: (the month_total can't be a seperate query.

<code>
select S.full_name, MONTH(deposit_date), sum(inv_amount), (select sum(inv_amount) from fin_data where deposit_date >= '2009-01-01'
and deposit_date <= '2009-04-30') as month_total
from users S
inner join fin_data SA on SA.closer_ID = S.ID
where deposit_date >= '2009-01-01'
and deposit_date <= '2009-04-01'
group by S.full_name, MONTH(deposit_date) order by month(deposit_date)
</code>

This returns to total for all months, not broken down by month. If I add a group by clause to the month_total select the whole sql breaks down.
0
 
Nathan RileyFounder/CTOCommented:
What error?

Try This:
select S.full_name, MONTH(deposit_date), sum(inv_amount), (select sum(inv_amount) from fin_data where deposit_date >= '2009-01-01' 
and deposit_date <= '2009-04-30' group by month(deposit_date)) as month_total
from users S
inner join fin_data SA on SA.closer_ID = S.ID
where deposit_date >= '2009-01-01'
and deposit_date <= '2009-04-01'
group by S.full_name, MONTH(deposit_date) order by month(deposit_date)

Open in new window

0
 
theseowgroupAuthor Commented:
Hi Gallitin,

I have tried that already exactly as you suggest but an error is returned:

"Cannot retrieve resultset data."

I don't think you can have a included select clause that groups as well.
0
 
Nathan RileyFounder/CTOCommented:
I don't think there is a way to do this then unless you split it into 2 different queries or have it in a stored procedure that inserts the result set into a table.
0
 
SharathData EngineerCommented:

Better provide some sample set and expected result.
0
 
theseowgroupAuthor Commented:
Yup, I think I am going to have to go with temp tables. Gallitin, thank you for your input. Really appreciate it but I think you are correct...... no way to do this in one query.
0
 
SharathData EngineerCommented:
why can't you provide some sample set. we can try doing it in one query.
0
 
theseowgroupAuthor Commented:
OK, I will do that tomorrow. Very late here now.
0
 
SharathData EngineerCommented:
no issues.
0
 
theseowgroupAuthor Commented:
There is no answer for this question
0
 
SharathData EngineerCommented:
If you want to close the question, you can close. But as I asked you before, provide some sample set and expected result. we can try.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now