Problem with Partition SQL Statement

mattkovo
mattkovo used Ask the Experts™
on
Here goes.  I have this query that is almost there but needs a little change. Because I am using the SUM statement I have to group and the partition statement is making me create another group. This makes the output group by 2 different fields but I only want to group by 1.  I would rather not use another Select statement to achieve the desired result but need some help here.

Here is what is being returned right now:
Name      Qty      Total Qty
Coke      3      24
Coke      6      24
Coke      7      24
Coke      8      24

Here is how I want it displayed:
Here is what is being returned right now:
Name      Qty      Total Qty
Coke      24      24

This is driving me crazy!


Select Distinct(Guest_check_name) as 'Name',
	   sum(Qty) as 'Qty',
	   sum(Qty) over (partition by 0) as 'Total Qty'
	   
from Item_Summary
	where storeid = 14
	and check_file_date between '7/1/2009' and '7/15/2009'
Group by Guest_Check_Name, Qty
order by Guest_Check_Name

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
But then why are you using OVER with the sum? Why not use it just like this?
Select Distinct 
	Guest_check_name as 'Name',
	sum(Qty) as 'Qty'
from Item_Summary
	where storeid = 14
	and check_file_date between '7/1/2009' and '7/15/2009'
Group by Guest_Check_Name
order by Guest_Check_Name

Open in new window

Or is this what you're looking for?
Select 	Guest_check_name as 'Name',
	sum(Qty) as 'Qty',
	(select sum(qty) from Item_summary where a.storeid = 14 and a.check_file_date between '7/1/2009' and '7/15/2009') 'Total Qty'
from Item_Summary
	where storeid = 14
	and check_file_date between '7/1/2009' and '7/15/2009'
Group by Guest_Check_Name
order by Guest_Check_Name

Open in new window

Author

Commented:
I was tryig to avoid using a nested Select statement. Looks like your second solution would work but having the partition statement would make the query run faster correct?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

The problem is that using Partition will give you the total per Guest_check_name, not the full total. so I don't see any other option but to use the nested select.
On a second thought maybe you are trying to do this:

Select Guest_check_name as 'Name',
	   sum(Qty) over (partition by Guest_check_name) as 'Qty',
	   sum(Qty) over (partition by 0) as 'Total Qty'
from Item_Summary
	where storeid = 14
	and check_file_date between '7/1/2009' and '7/15/2009'
order by Guest_Check_Name

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
mattkovo,
      In your query, you have involved the same column Qty to find Qty and Total Qty which makes both are one and the same.

Hence you can do it something like the one below to achieve your desired result set.
If you involve the same column, then both will give 24 and 24 ( as in your example) and what's the purpose of it?

Does your requirement differs ..
If you , kindly post some sample records for Coke present in Item_summary table along with the desired result set to help us understand and guide you better.


Select Guest_check_name as 'Name',
           sum(Qty) over (partition by 0) as 'Qty',
           sum(Qty) over (partition by 0) as 'Total Qty'
from Item_Summary
        where storeid = 14
        and check_file_date between '7/1/2009' and '7/15/2009'
Group by Guest_Check_Name
order by Guest_Check_Name

Open in new window

I disagree. I have provided you with two alternatives. Please see comments 24882211 and 24883726. Now if you think there is something I haven't addressed, please post a better example with sample data and expected result.
My recommendation is accept comments 24882211 and 24883726 as the answer

Author

Commented:
No valid solutions have been provided.

ID: 24883726: combines the entire Total Qty
ID: 24882211: can not be bound errors

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial