EDIT - i'm reposting this question in an attempt to explain what i mean better
I'm using SQL 2008 R2 and I work for a retail department store and we need a report to show all the sales orders made in each department, and sections of those departments.
What i want is to group up all the sales order lines by department and section, but remove only the sections that have a total sales value of less than £50. I still want to see order lines that are over £50, though.
Here is an example of what i currently have:
sales_order_detail INNER JOIN stock_item ON sales_order_detail.stock_item_code = stock_item.stock_item_code
INNER JOIN style ON stock_item.style_code = style.style_code
INNER JOIN department ON style.dept_code = department.department_code
INNER JOIN section ON style.section_code = section.section_code AND style.dept_code = section.department_code AND department.department_code = section.department_code
As you are using 2008R2, you can use the magic that are Windowed Functions to calculate the total of the group that the row belongs to (the
partition part of the
over clause below) and then wrap your query into a filtering
select statement. Not having your data this is obviously not tested, but it should work:
select department_name ,section_name ,sales_order_number ,sales_order_line ,LineValue ,GroupTotal from( select d.department_name ,se.section_name ,sod.sales_order_number ,sod.sales_order_line ,sod.qty_ordered * sod.selling_price AS LineValue ,sum(sod.qty_ordered * sod.selling_price) over (partition by d.department_name ,se.section_name ) as GroupTotal from sales_order_detail sod inner join stock_item si on sod.stock_item_code = si.stock_item_code inner join style s on stock_item.style_code = s.style_code inner join department d on s.dept_code = d.department_code inner join section se on s.section_code = se.section_code and s.dept_code = se.department_code and d.department_code = se.department_code ) a where GroupTotal > 50