July 17, 2008 at 4:16 am
Hello, I am trying to get this query up and running, where i am having problems, is at the last line (([Unit Price] * QuantityShipped) / QuantityShipped AS [A.S.P]) where i am trying to calculate the average selling price which is basically the sum of unit price multiplied by the qty shipped divided by the qty shipped.
SELECTCONVERT(varchar(11), ShipDate, 106) AS [Ship Date],ParentProductClassID , CatalogueID, Description, FitID,
SizeID, Cuff,
SUM(Quantity)AS [Total Qty], SUM(QuantityShipped)AS [Total Qty Shipped],
SUM(ExtendedPrice)AS [Total Extended Price],
(SUM([Unit Price] * QuantityShipped) / QuantityShipped) AS [A.S.P]
FROM vwSTOCKTYPESALES
GROUP BY ShipDate,ParentProductClassID, CatalogueID, Description, FitID, SizeID, Cuff
ORDER BY ShipDate DESC
can anyone help?
July 17, 2008 at 4:28 am
what kind of problem you have?
July 17, 2008 at 4:33 am
Your aggregate is in the wrong place.
Your code = (SUM([Unit Price] * QuantityShipped) / QuantityShipped) AS [A.S.P]
SUM should be on the outside of the parens, not inside the first one.
I.E., SUM( ([Unit Price] * QuantityShipped) / QuantityShipped) AS [A.S.P]
Does that help?
EDIT: WHOOPS. Just realized what you're trying to do. Make that:
( SUM([Unit Price]) * QuantityShipped) / QuantityShipped as [A.S.P]
And you might want to put a Zero check on the Quantity Shipped do to Divide By Zero Errors, plus an ISNULL() on the Unit Price, which would make your code...
...
Case QuantityShipped WHEN 0 THEN 0
ELSE ( SUM(ISNULL([Unit Price],0)) * QuantityShipped)
/ QuantityShipped END as [A.S.P]
...
July 17, 2008 at 4:48 am
This would be my guess at what you're trying to do...
SELECT CONVERT(varchar(11), ShipDate, 106) AS [Ship Date],ParentProductClassID , CatalogueID, Description, FitID,
SizeID, Cuff,
SUM(Quantity)AS [Total Qty], SUM(QuantityShipped)AS [Total Qty Shipped],
SUM(ExtendedPrice)AS [Total Extended Price],
(SUM([Unit Price] * QuantityShipped) / SUM(QuantityShipped)) AS [A.S.P]
FROM vwSTOCKTYPESALES
GROUP BY ShipDate,ParentProductClassID, CatalogueID, Description, FitID, SizeID, Cuff
ORDER BY ShipDate DESC
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 17, 2008 at 4:54 am
Brandie Tarvin (7/17/2008)
...
Case QuantityShipped WHEN 0 THEN 0
ELSE ( SUM(ISNULL([Unit Price],0)) * QuantityShipped)
/ QuantityShipped END as [A.S.P]
...
I think this will error because QuantityShipped is not in the group by. The checking is a good idea though - perhaps something like...
CASE WHEN ISNULL(SUM(QuantityShipped), 0) = 0 THEN NULL
ELSE SUM([Unit Price] * QuantityShipped) / SUM(QuantityShipped) END AS [A.S.P]
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 17, 2008 at 5:01 am
Ryan,
If you read his first post, he doesn't want to SUM the QuantityShipped. And I was only addressing the math problem, not the GROUP BY (although I should have looked at that too @=).
But yes, he does need to add QuantityShipped to the Group By.
July 17, 2008 at 5:53 am
I agree with the solution given by RyanRandall.
Here is an reduce example:
CREATE TABLE vwSTOCKTYPESALES
([Unit Price]decimal(15,2),
QuantityShippedint,
CatalogueIDint)
INSERT INTO vwSTOCKTYPESALES VALUES (7.50, 10, 1)
INSERT INTO vwSTOCKTYPESALES VALUES (4.75, 12, 1)
INSERT INTO vwSTOCKTYPESALES VALUES (6.40, 18, 1)
INSERT INTO vwSTOCKTYPESALES VALUES (7.25, 08, 1)
INSERT INTO vwSTOCKTYPESALES VALUES (6.00, 08, 2)
INSERT INTO vwSTOCKTYPESALES VALUES (6.00, 08, 2)
INSERT INTO vwSTOCKTYPESALES VALUES (5.40, 12, 2)
INSERT INTO vwSTOCKTYPESALES VALUES (5.40, 12, 2)
select CatalogueID, (SUM([Unit Price] * QuantityShipped) / SUM(QuantityShipped)) AS [A.S.P]
from vwSTOCKTYPESALES
group by CatalogueID
And the results are:
CatalogueID A.S.P
----------- ----------------------------------------
1 6.358333
2 5.640000
(2 row(s) affected)
I think this is the correct solution.
July 18, 2008 at 3:41 am
Hi All
I used the query this way and i got an error message saying incorrect syntax near Price on line 4, can anyone help?
SELECT ParentProductClassID , CatalogueID, Description, FitID,
SizeID, Cuff, QuantityShipped,
SUM(Quantity)AS [Total Qty], SUM(QuantityShipped)AS [Total Qty Shipped],
SUM(Extended Price) AS [Total Extended Price],
Case QuantityShipped WHEN 0 THEN 0 ELSE ( SUM(ISNULL([Unit Price],0)) * QuantityShipped)
/ QuantityShipped END as [A.S.P]
FROM StockSales
GROUP BY ParentProductClassID, CatalogueID, Description, FitID, SizeID, Cuff,QuantityShipped
July 18, 2008 at 3:49 am
The error is because you need square brackets around 'Extended Price' on line 4.
SUM([Extended Price]) AS [Total Extended Price],
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 18, 2008 at 3:55 am
Ralph
Your a life saver thanks very much!!!
July 18, 2008 at 4:22 am
b_boy (7/18/2008)
RalphYour a life saver thanks very much!!!
Ralph?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 18, 2008 at 5:30 am
:w00t: I meant Ryan!!
July 18, 2008 at 5:40 am
Isn't Ralph the name of your evil twin brother, Ryan? @=)
July 23, 2008 at 10:21 am
Hey All - Im back,lol
I just rewrote this query and i get the following error message:
Column 'CMDATA_Reporting.dbo.T_ProductClass.ParentProductClassID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Can anyone help me?
--Query--
SELECTT_ProductClass.ParentProductClassID, T_ProductClass.ProductClassID, T_ProductClass.ProdClassDesc,
T_Items.CatalogueID, T_Items.Description,T_Items.Look AS Cuff,T_Items.ColourID, T_Items.StockID, T_OrderLine.Quantity,
T_Items.RRP,T_OrderLine.[Unit Price],
T_OrderLine.QuantityShipped, T_OrderLine.QuantityReturned, T_OrderLine.ShipDate, T_OrderLine.ExtendedPrice,
T_Items.SizeID, T_Items.FitID, T_Items.ProductClassID AS [TI PRODUCT], T_Items.Tree,
QuantityShipped - QuantityReturned AS [Net Sale],
(T_OrderLine.[Unit Price] * (T_OrderLine.QuantityShipped - T_OrderLine.QuantityReturned)) AS [Total Cash],
(SUM(T_OrderLine.[Unit Price] * T_OrderLine.QuantityShipped) / SUM(T_OrderLine.QuantityShipped)) AS [A.S.P]
FROM(CMDATA_Reporting.dbo.T_OrderLine T_OrderLine
INNER JOINCMDATA_Reporting.dbo.T_Items T_Items
ONT_OrderLine.StockID=T_Items.StockID)
LEFT OUTER JOIN CMDATA_Reporting.dbo.T_ProductClass T_ProductClass
ONT_Items.ProductClassID = T_ProductClass.ProductClassID
ORDER BYT_ProductClass.ParentProductClassID, T_Items.ProductClassID, T_Items.CatalogueID, T_Items.StockID
July 23, 2008 at 10:41 am
The error message means exactly what it says. Right above your ORDER BY, you need a GROUP BY statement that lists out every field that is not aggregated in some form, or any field used in a CASE statement (whether or not it's aggregated).
In this case, you need to add the following fields to the GROUP BY:
T_ProductClass.ParentProductClassID, T_ProductClass.ProductClassID, T_ProductClass.ProdClassDesc, T_Items.CatalogueID, T_Items.Description,T_Items.Look,T_Items.ColourID, T_Items.StockID, T_OrderLine.Quantity, T_Items.RRP,T_OrderLine.[Unit Price], T_OrderLine.QuantityShipped, T_OrderLine.QuantityReturned, T_OrderLine.ShipDate, T_OrderLine.ExtendedPrice, T_Items.SizeID, T_Items.FitID, T_Items.ProductClassID, T_Items.Tree
Make sure NOT to put the aliases in the GROUP BY or you will get additional errors.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply