April 10, 2008 at 4:35 am
I created a Pivot Table that totals the Products ordered for each month in a pivot Table. is there possible to Display Totals of Products and Totals of Values for Each month in a single Pivot Table??
April 10, 2008 at 4:58 am
vblllove (4/10/2008)
I created a Pivot Table that totals the Products ordered for each month in a pivot Table. is there possible to Display Totals of Products and Totals of Values for Each month in a single Pivot Table??
Could you provide an example? Maybe even some test data and your current query? And the expected results
Regards,
Andras
April 10, 2008 at 5:41 am
here is a sample of the Query . From the query below Im getting only the Totals Order Amount in Dollars for each Product quarterly.
I want to display also the sum for OrderQty ( using AdventureWorks Database as Sample) for each quarter next to the Order Amount.
USE AdventureWorks;
WITH cteQuarterSales
AS
(SELECT DatePart(qq,OrderDate) as Quarter, p.Name,
OrderAmount = convert(money, LineTotal)
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN Production.Product AS p
ON sod.ProductID = p.ProductID
WHERE DatePart(yy,OrderDate) = 2001)
SELECT ProductName=Name,
'$'+convert(varchar,
isnull([1],0)+isnull([2],0)+isnull([3],0)+isnull([4],0),1) AS '2001',
'$'+convert(varchar,isnull([1],0),1) AS 'Q1',
'$'+convert(varchar,isnull([2],0),1) AS 'Q2',
'$'+convert(varchar,isnull([3],0),1) AS 'Q3',
'$'+convert(varchar,isnull([4],0),1) AS 'Q4'
FROM cteQuarterSales
PIVOT(SUM(OrderAmount)
FOR Quarter IN ([1], [2], [3], [4])) AS P
ORDER BY ProductName
So simply cange this
Product Name 2001 Q1 Q2 Q3 Q4
AWC Logo Cap$2,686.87 $0.00$0.00$1,168.60$1,518.27
to
Product Name 2001 Q1 Q2 Q3 Q4
AWC Logo Cap$2,686.87 7$0.00 0$0.00 0 $1,168.60 3 $1,518.27 4
thanks in advance
April 10, 2008 at 7:04 am
You essentially need to run the pivot two seperate times and join on the product name. This may not be the most efficient way of solving your problem, but this solution should help.
I've had to do something similar to this before, but it involved days of the month and required the use of a ROW_NUMBER() being used. In your example the Product Name will suffice.
USE AdventureWorks;
WITH cteQuarterSales
AS (SELECT
DATEPART(qq, OrderDate) AS Quarter
,p.[Name]
,OrderAmount = CONVERT(MONEY, LineTotal)
--,OrderQuantity = OrderQty
FROM
Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN Production.Product AS p
ON sod.ProductID = p.ProductID
WHERE
DATEPART(yy, OrderDate) = 2001)
, orderAmount
AS (SELECT
ProductName = [Name]
,'$' + CONVERT(VARCHAR, ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0) + ISNULL([4], 0), 1) AS '2001_Amount'
,'$' + CONVERT(VARCHAR, ISNULL([1], 0), 1) AS 'Q1_Amount'
,'$' + CONVERT(VARCHAR, ISNULL([2], 0), 1) AS 'Q2_Amount'
,'$' + CONVERT(VARCHAR, ISNULL([3], 0), 1) AS 'Q3_Amount'
,'$' + CONVERT(VARCHAR, ISNULL([4], 0), 1) AS 'Q4_Amount'
FROM
cteQuarterSales
PIVOT ( SUM(OrderAmount)
FOR Quarter IN ([1], [2], [3], [4]) ) AS P)
,cteQuarterQty
AS (SELECT
DATEPART(qq, OrderDate) AS Quarter
,p.[Name]
,OrderQuantity = OrderQty
FROM
Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN Production.Product AS p
ON sod.ProductID = p.ProductID
WHERE
DATEPART(yy, OrderDate) = 2001)
, orderQuantity
AS (SELECT
ProductName = [Name]
,CONVERT(VARCHAR, ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0) + ISNULL([4], 0), 1) AS '2001_Quantity'
,CONVERT(VARCHAR, ISNULL([1], 0), 1) AS 'Q1_Quantity'
,CONVERT(VARCHAR, ISNULL([2], 0), 1) AS 'Q2_Quantity'
,CONVERT(VARCHAR, ISNULL([3], 0), 1) AS 'Q3_Quantity'
,CONVERT(VARCHAR, ISNULL([4], 0), 1) AS 'Q4_Quantity'
FROM
cteQuarterQty
PIVOT ( SUM(orderQuantity)
FOR Quarter IN ([1], [2], [3], [4]) ) AS P)
SELECT
a.ProductName
,2001_Amount,2001_Quantity
,Q1_Amount,Q1_Quantity
,Q2_Amount,Q2_Quantity
,Q3_Amount,Q3_Quantity
,Q4_Amount,Q4_Quantity
FROM
orderAmount AS a
LEFT JOIN orderQuantity AS b
ON a.ProductName = b.ProductName
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 10, 2008 at 7:09 am
You would need to use two pivots, e.g.
WITH cteQuarterSales
AS ( SELECT DATEPART(qq, OrderDate) AS Quarter,
p.Name,
OrderAmount = CONVERT(MONEY, LineTotal),
OrderQty
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN Production.Product AS p ON sod.ProductID = p.ProductID
WHERE DATEPART(yy, OrderDate) = 2001
)
SELECT PP.ProductName,
PP.[2001],
Q1,
Q1_P,
Q2,
Q2_P,
Q3,
Q3_P,
Q4,
Q4_P
FROM ( SELECT ProductName = Name,
'$'
+ CONVERT(VARCHAR, ISNULL([1], 0) + ISNULL([2], 0)
+ ISNULL([3], 0) + ISNULL([4], 0), 1) AS '2001',
'$' + CONVERT(VARCHAR, ISNULL([1], 0), 1) AS 'Q1',
'$' + CONVERT(VARCHAR, ISNULL([2], 0), 1) AS 'Q2',
'$' + CONVERT(VARCHAR, ISNULL([3], 0), 1) AS 'Q3',
'$' + CONVERT(VARCHAR, ISNULL([4], 0), 1) AS 'Q4'
FROM cteQuarterSales PIVOT ( SUM(OrderAmount) FOR Quarter IN ( [1], [2], [3], [4] ) ) AS P ) AS PP
JOIN ( SELECT ProductName = Name,
'$'
+ CONVERT(VARCHAR, ISNULL([1], 0) + ISNULL([2], 0)
+ ISNULL([3], 0) + ISNULL([4], 0), 1) AS '2001',
[1] AS 'Q1_P',
[2] AS 'Q2_P',
[3] AS 'Q3_P',
[4] AS 'Q4_P'
FROM cteQuarterSales PIVOT ( SUM(OrderQty) FOR Quarter IN ( [1], [2], [3], [4] ) ) AS Q
) AS QQ ON QQ.ProductName = PP.ProductName
ORDER BY PP.ProductName
Regards,
Andras
April 10, 2008 at 7:10 am
Oops, Jason was faster 🙂
(one difference is in our solutions is that it is enough to have one CTE expression), so you now have two solutions 🙂
Regards,
Andras
April 10, 2008 at 7:16 am
I hate to be the bearor of bad news Andras, but your query produces a cartesean product. :ermm: Your query returns some 3000 rows, and mine is the original 64. (Using my AdventureWorks DB that is)
I actually ran into that while writing mine, and had to handle it differently.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 10, 2008 at 8:17 am
Jason Selburg (4/10/2008)
I hate to be the bearor of bad news Andras, but your query produces a cartesean product. :ermm: Your query returns some 3000 rows, and mine is the original 64. (Using my AdventureWorks DB that is)I actually ran into that while writing mine, and had to handle it differently.
Ooops, schoolboy error. Thanks for pointing this out, (I really should have checked the script better)
Andras
April 10, 2008 at 8:58 am
HI Jason
thanks for the Query but I still dont get the Expected results. It repeats the same product name for the same client three times?????
Seems like the ProductName soesnt work as a link
April 10, 2008 at 9:11 am
Really? IT works on my Adventure works DB just fine.
Are you using the EXACT code I posted?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 10, 2008 at 9:44 am
For somehow it doesnt work on my database. Actually i display the klient code Also( The code of the person that Sell this products) When I have more than One Klient that Sell this Product I get repeated three times. ????
April 10, 2008 at 9:53 am
That would explain it. It's because you add in another element to the Pivot without aggregating it. You'll need to remove that column from the results or comma delimeted list (25,61,3,54) of the SalesperonsIDs if you really need them listed on one row.
I've got to back out of this topic at this point, but I'm sure someone else can/will help you further.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 11, 2008 at 2:06 am
Thanks Jason ,
I got it working by using RowNumber = ROW_NUMBER() OVER(Order BY ProductName)
Thanks everyone for all your help
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply