I'm looking for a way to summarize the sales figures in a table containing about 75,000 records. I'd like to end up with one row for each sales rep and 2 columns for each of the following price "buckets": $0-1,000 and $1-3,000 and $3-10,000 and $10,000+. For each of those ranges, I need to know the percentage of orders for that rep that fall within that range, and a count of how many orders for that rep that fall into that range (these are the 2 columns per bucket).
I need to exclude all orders with totalprice < $10. And I need to pass in a start date and an end date. But sometimes this range will include everything in the table.
The tables are: order_entry with PK job_number (int), totalprice (decimal), entry_date (datetime), and sales_rep_emp_number (int). And I would like to display the rep's names which are in the employees table with PK employee_number (int)
If it's easy to include the following grand total columns as the last columns, that would also be awesome: sum of all sales for that rep, count of all sales, average sale price.
All that, and a chocolate donut, would absolutely make my day.
Start Free Trial
