May 16, 2008 at 5:02 am
Hi all,
I'm encountering an issue with using a SUM() aggregate in the select statement of a pivot query I am attempting. Here is the structure of the query:
Select UID, Sum(Weight) as Weight, CustomerName, RecycleDate
from (
SELECT p.UID, case when tblItemType.ItemTypeID in (1,3,4,8,9) then tblItemType.Description else 'Other' end as ItemType, SUM(tblItems.Quantity) as Quantity,
SUM(tblItems.Weight) as Weight, c.CustomerName, p.RecycleDate
FROM tblPallet p
INNER JOIN tblItems ON tblItems.RecyclePalletID = p.PalletID
INNER JOIN tblItemType ON tblItems.ItemTypeID = dbo.tblItemType.ItemTypeID
INNER JOIN tblCustomer c on p.PalletRecyclerID = c.CustomerID
Where p.UID = @UID
group by p.UID, c.CustomerName, p.RecycleDate,
tblItemType.ItemTypeID, tblItemType.Description
UNION ALL
SELECT p.UID, case when tblItemType.ItemTypeID in (1,3,4,8,9) then tblItemType.Description else 'Other' end as ItemType, Count(ItemDetailID) as Quantity,
SUM(tblItemsResell.Weight) as Weight, c.CustomerName, p.RecycleDate
FROM tblPallet p
INNER JOIN tblItemsResell ON tblItemsResell.RecyclePalletID = p.PalletID
INNER JOIN tblModel mo on tblItemsProcessedResell.ModelID = mo.ModelID
INNER JOIN tblItemType ON mo.ItemTypeID = dbo.tblItemType.ItemTypeID
INNER JOIN tblCustomer c on p.PalletRecyclerID = c.CustomerID
WHERE p.UID = @PalletID
group by p.UID, c.CustomerName, p.RecycleDate,
tblItemType.ItemTypeID, tblItemType.Description
)
t
pivot
(
sum(Quantity)
for ItemType
in (Laptop, BaseUnit, Monitor, Printer, Scanner, Misc, Other)
) as p
GROUP BY UID, CustomerName, RecycleDate
ORDER BY UID
The inner query returns a result set like:
UID Weight CustomerName RecycleDate ItemType Quantity
1 50 Customer1 01/01/2008 ItemType1 10
2 50 Customer2 01/01/2008 ItemType2 20
3 50 Customer3 01/01/2008 ItemType2 30
The desired output is to display the total sum of the weight against the pivot results, which will show the quantities of items split by item type.
The problem is that SUM(Weight) does not seem to work for instances when the weight values between rows are identical! For the dataset above, I would be expecting a sum weight of 150 - but the query outputs a weight of 50.
Can anyone help me as to why this is occurring? Is what I am attempting to do a) possible b) practical?
Thanks for looking.
Paul.
May 16, 2008 at 11:31 pm
The problem is that you are grouping by the UID which appears to be unique... it won't give you the sum of 150 that way... it will always list the unique rows...
Please post what the output should look like... you might want to take a look at the URL in my signature, as well... you might get a better answer.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 20, 2008 at 4:58 am
The unique ID is not unique in this case because I am selecting from 2 different queries and using a union.
The problem is that it produces an output like this:
UID Weight etc....
1000 50
1000 50
When I run the pivot query as above using SUM(Weight) and grouping by UID, I get the following:
UID Weight
1000 50
I may be going bonkers but surely this should return a weight of 100?
May 20, 2008 at 7:28 am
You would get 100 if you weren't also grouping by customer name and date as in your original code.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 20, 2008 at 9:21 am
.. but if customername and date are identical (and they will be in many instances), should this not sum to give 100?
I may have confused you a little by attempting to simplify the script to make it easier to understand. I can put up the full script if you like.
May 20, 2008 at 9:36 am
I'm actually trying to figure out why you're doing sums both in the inner query and the outer query. It looks to me that you're making this substantially harder than it needs to be, and possibly hiding the error by making it so much harder.
Should be more like this:
Select UID,
Sum(Weight) as Weight,
CustomerName,
RecycleDate,
[Laptop],
[BaseUnit],
[Monitor],
[Printer],
[Scanner],
[Misc],
[Other]
from (
SELECT p.UID,
case when tblItemType.ItemTypeID in (1,3,4,8,9) then tblItemType.Description else 'Other' end as ItemType,
tblItems.Quantity as Quantity,
tblItems.Weight as Weight,
c.CustomerName,
p.RecycleDate
FROM tblPallet p
INNER JOIN tblItems ON tblItems.RecyclePalletID = p.PalletID
INNER JOIN tblItemType ON tblItems.ItemTypeID = dbo.tblItemType.ItemTypeID
INNER JOIN tblCustomer c on p.PalletRecyclerID = c.CustomerID
Where p.UID = @UID
UNION ALL
SELECT p.UID,
case when tblItemType.ItemTypeID in (1,3,4,8,9) then tblItemType.Description else 'Other' end as ItemType,
1 as Quantity,
tblItemsResell.Weight as Weight,
c.CustomerName,
p.RecycleDate
FROM tblPallet p
INNER JOIN tblItemsResell ON tblItemsResell.RecyclePalletID = p.PalletID
INNER JOIN tblModel mo on tblItemsProcessedResell.ModelID = mo.ModelID
INNER JOIN tblItemType ON mo.ItemTypeID = dbo.tblItemType.ItemTypeID
INNER JOIN tblCustomer c on p.PalletRecyclerID = c.CustomerID
WHERE p.UID = @PalletID
) t
pivot
(
sum(Quantity)
for ItemType
in ([Laptop], [BaseUnit], [Monitor], [Printer], [Scanner], [Misc], [Other])
as p
GROUP BY UID, CustomerName, RecycleDate
ORDER BY UID
If this still doesn't give you waht you're looking for - try giving us some actual data as an example to work with. Follow those guidelines Jeff put together for putting that up (it helps a LOT).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 20, 2008 at 9:41 am
Hi Matt,
Thanks for the reply. This is a query I've inherited along with an application and I'm still trying to understand a lot of it 😀
You should see some of the views... :w00t:
I'll try modifying it as you suggest and see if that will help.
May 21, 2008 at 9:54 am
Hi Matt,
Afraid this didn't work and am having exactly the same issue. I'll stick up some data for this tomorrow following Jeff's guidelines if I can.
June 24, 2008 at 7:19 pm
Paul McKay (5/21/2008)
I'll stick up some data for this tomorrow following Jeff's guidelines if I can.
Is it tomorrow yet? 😉
Just teasing... did you ever get this figured out, Paul?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2008 at 3:17 am
Hi Jeff,
I did indeed.
I basically deconstructed the entire query and re-wrote it the way I would.
I believe the issue was caused by a join in the interior query. I worked around this by putting the join outside the query and onto the select statement which had the pivot query.
This removed the need for the grouping which had caused the initial problem and made the query run faster 😀
Thanks for the advice though.
Paul.
June 25, 2008 at 5:12 am
Paul McKay (6/25/2008)
I basically deconstructed the entire query and re-wrote it the way I would.
That is sometimes the best and easiest thing to do. Thanks for the feedback, Paul.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply