Crosstab query? 2

[primagic]

I am trying to create a query where the results will be displayed like:

Product Ordered Sold Stock Remaining
Trader A 1 5 3 2
Trader B 1 4 1 3

At the moment the information is contained in tables:

Sales Table which contains the TraderID field. this is linked to the salesdetails table which contains all the information, product sold, quantity, amount and so on.

Then there is an orders table, which has the traderID and linked to the orders details table which contains information relating to product, quantity and cost price.

At the moment I have two queries, which are:

For Orders

SELECT     dbo.Orders.TraderID, dbo.OrderDetails.Product, SUM(dbo.OrderDetails.Quantity) AS TotalOrdered
FROM         dbo.OrderDetails INNER JOIN
                      dbo.Orders ON dbo.OrderDetails.OrderID = dbo.Orders.OrderID
GROUP BY dbo.Orders.TraderID, dbo.OrderDetails.Product

And for Sales

SELECT     dbo.Sales.TraderID, dbo.SalesDetails.ProductID, SUM(dbo.SalesDetails.Quantity) AS TotalSold
FROM         dbo.Sales LEFT OUTER JOIN
                      dbo.SalesDetails ON dbo.Sales.SalesID = dbo.SalesDetails.SalesID
GROUP BY dbo.Sales.TraderID, dbo.SalesDetails.ProductID

These queries display the correct information in the correct format:

TraderID Product Quantity(Sum)

But I cant get the two queries merged together where the information can be displayed like my table example above

 

[sconti11]

You could join the two sql statements together using derived tables.

So you could do somthing like this:

Select a.TraderID, a.Product, a.TotalOrdered, b.ProductID, b.TotalSold
From
(SELECT dbrders.TraderID, dbrderDetails.Product, SUM(dbrderDetails.Quantity) AS TotalOrdered FROM dbrderDetails INNER JOIN dbrders ON dbrderDetails.OrderID = dbrders.OrderID GROUP BY dbrders.TraderID, dbrderDetails.Product) as a FULL OUTER JOIN
(SELECT dbo.Sales.TraderID, dbo.SalesDetails.ProductID, SUM(dbo.SalesDetails.Quantity) AS TotalSold FROM dbo.Sales LEFT OUTER JOIN dbo.SalesDetails ON dbo.Sales.SalesID = dbo.SalesDetails.SalesIDGROUP BY dbo.Sales.TraderID, dbo.SalesDetails.ProductID) as b ON a.TraderID = b.TraderID

 

[primagic]

Thank you for that. I have tried creating a query with the code above and I get the following error:

Incorrect syntax near the keyword 'BY'.

 

[primagic]

Got it,

error was

dbo.SalesDetails.SalesIDGROUP BY

No space

Trader  Product Ordered Product Sold
12345	1	1	NULL	NULL
28372	1	1	3	1
28372	1	1	4	1
28372	1	1	26	2
28372	1	1	105	2
28372	1	1	112	1
28372	1	1	126	1
28372	1	1	660	3
28372	1	1	753	1
28372	1	1	435	2
28372	1	1	442	1
28372	1	1	447	1
28372	1	1	478	1
28372	1	1	479	1
28372	1	1	508	1
28372	1	1	362	1
28372	1	1	363	2
28372	1	1	370	1
28372	1	1	398	1
28372	1	1	410	1
28372	1	1	429	1
28372	1	1	296	1
28372	1	1	309	1
28372	1	1	316	1
28372	1	1	321	1
28372	1	1	336	1
28372	1	1	356	1
28372	1	1	217	1
28372	1	1	231	1
28372	1	1	241	1
28372	1	1	245	1
28372	1	1	267	1
28372	1	1	268	1
28372	1	1	127	1
28372	1	1	133	2
28372	1	1	141	1
28372	1	1	143	1
28372	1	1	160	2
28372	1	1	208	1
28372	2	1	3	1
28372	2	1	4	1
28372	2	1	26	2
28372	2	1	105	2
28372	2	1	112	1
28372	2	1	126	1
28372	2	1	660	3
28372	2	1	753	1
28372	2	1	435	2
28372	2	1	442	1
28372	2	1	447	1
28372	2	1	478	1
28372	2	1	479	1
28372	2	1	508	1
28372	2	1	362	1
28372	2	1	363	2
28372	2	1	370	1
28372	2	1	398	1
28372	2	1	410	1
28372	2	1	429	1
28372	2	1	296	1
28372	2	1	309	1
28372	2	1	316	1
28372	2	1	321	1
28372	2	1	336	1
28372	2	1	356	1
28372	2	1	217	1
28372	2	1	231	1
28372	2	1	241	1
28372	2	1	245	1
28372	2	1	267	1
28372	2	1	268	1
28372	2	1	127	1
28372	2	1	133	2
28372	2	1	141	1
28372	2	1	143	1
28372	2	1	160	2
28372	2	1	208	1
28372	3	1	3	1
28372	3	1	4	1
28372	3	1	26	2
28372	3	1	105	2
28372	3	1	112	1
28372	3	1	126	1
28372	3	1	660	3
28372	3	1	753	1
28372	3	1	435	2
28372	3	1	442	1
28372	3	1	447	1
28372	3	1	478	1
28372	3	1	479	1
28372	3	1	508	1
28372	3	1	362	1
28372	3	1	363	2
28372	3	1	370	1
28372	3	1	398	1
28372	3	1	410	1
28372	3	1	429	1
28372	3	1	296	1
28372	3	1	309	1
28372	3	1	316	1
28372	3	1	321	1
28372	3	1	336	1
28372	3	1	356	1
28372	3	1	217	1
28372	3	1	231	1
28372	3	1	241	1
28372	3	1	245	1
28372	3	1	267	1
28372	3	1	268	1
28372	3	1	127	1
28372	3	1	133	2
28372	3	1	141	1
28372	3	1	143	1
28372	3	1	160	2
28372	3	1	208	1
28372	8	1	3	1
28372	8	1	4	1
28372	8	1	26	2
28372	8	1	105	2
28372	8	1	112	1
28372	8	1	126	1
28372	8	1	660	3
28372	8	1	753	1
28372	8	1	435	2
28372	8	1	442	1
28372	8	1	447	1
28372	8	1	478	1
28372	8	1	479	1
28372	8	1	508	1
28372	8	1	362	1
28372	8	1	363	2
28372	8	1	370	1
28372	8	1	398	1
28372	8	1	410	1
28372	8	1	429	1
28372	8	1	296	1
28372	8	1	309	1
28372	8	1	316	1
28372	8	1	321	1
28372	8	1	336	1
28372	8	1	356	1
28372	8	1	217	1
28372	8	1	231	1
28372	8	1	241	1
28372	8	1	245	1
28372	8	1	267	1
28372	8	1	268	1
28372	8	1	127	1
28372	8	1	133	2
28372	8	1	141	1
28372	8	1	143	1
28372	8	1	160	2
28372	8	1	208	1
28372	9	1	3	1
28372	9	1	4	1
28372	9	1	26	2
28372	9	1	105	2
28372	9	1	112	1
28372	9	1	126	1
28372	9	1	660	3
28372	9	1	753	1
28372	9	1	435	2
28372	9	1	442	1
28372	9	1	447	1
28372	9	1	478	1
28372	9	1	479	1
28372	9	1	508	1
28372	9	1	362	1
28372	9	1	363	2
28372	9	1	370	1
28372	9	1	398	1
28372	9	1	410	1
28372	9	1	429	1
28372	9	1	296	1
28372	9	1	309	1
28372	9	1	316	1
28372	9	1	321	1
28372	9	1	336	1
28372	9	1	356	1
28372	9	1	217	1
28372	9	1	231	1
28372	9	1	241	1
28372	9	1	245	1
28372	9	1	267	1
28372	9	1	268	1
28372	9	1	127	1
28372	9	1	133	2
28372	9	1	141	1
28372	9	1	143	1
28372	9	1	160	2
28372	9	1	208	1

This is the results of the query, its showing the product multiple times? Do I need to adding anything or is it possible to get it so it shows

TraderID    Product   Ordered    Sold

 

[sconti11]

Can you send me the exact sql as you have it?

If you copied & pasted my code into your sql then maybe there was some pieces of the code that did not get translated correctly.

 

[primagic]

Select a.TraderID, a.Product, a.TotalOrdered, b.ProductID, b.TotalSold
From
(SELECT dbo.Orders.TraderID, dbo.OrderDetails.Product, SUM(dbo.OrderDetails.Quantity) AS TotalOrdered FROM dbo.OrderDetails INNER JOIN dbo.Orders ON dbo.OrderDetails.OrderID = dbo.Orders.OrderID GROUP BY dbo.Orders.TraderID, dbo.OrderDetails.Product) as a FULL OUTER JOIN
(SELECT dbo.Sales.TraderID, dbo.SalesDetails.ProductID, SUM(dbo.SalesDetails.Quantity) AS TotalSold FROM dbo.Sales LEFT OUTER JOIN dbo.SalesDetails ON dbo.Sales.SalesID = dbo.SalesDetails.SalesID GROUP BY dbo.Sales.TraderID, dbo.SalesDetails.ProductID) as b ON a.TraderID = b.TraderID

 

[gmmastros]

I think you missed a join condition.

Select a.TraderID, a.Product, a.TotalOrdered, b.ProductID, b.TotalSold
From
(SELECT dbo.Orders.TraderID, dbo.OrderDetails.Product, SUM(dbo.OrderDetails.Quantity) AS TotalOrdered FROM dbo.OrderDetails INNER JOIN dbo.Orders ON dbo.OrderDetails.OrderID = dbo.Orders.OrderID GROUP BY dbo.Orders.TraderID, dbo.OrderDetails.Product) as a FULL OUTER JOIN
(SELECT dbo.Sales.TraderID, dbo.SalesDetails.ProductID, SUM(dbo.SalesDetails.Quantity) AS TotalSold FROM dbo.Sales LEFT OUTER JOIN dbo.SalesDetails ON dbo.Sales.SalesID = dbo.SalesDetails.SalesID GROUP BY dbo.Sales.TraderID, dbo.SalesDetails.ProductID) as b ON a.TraderID = b.TraderID [!] And a.Product = b.ProductId[/!]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[primagic]

That worked gmmastros. Thank you.

Thanks sconti11 for your initial help. Much appreciated.

 

[primagic]

Would there be a reason why I have Null entries in the traderid, product, total ordered fields but amounts in the productid, sold columns.

so no corresponding traderid? however these products have been sold against a sale record linked to a trader.

Likewise, I have entries in the traderid, product and total ordered fields but NULLS in productid and sold. SHould the product field not be the same as the productid field.

Lastly, converting NULLS to 0's in the query, how would I do this.

Thanks again

 

[gmmastros]

Would there be a reason why I have Null entries in the traderid, product, total ordered fields but amounts in the productid, sold columns.

Of course there is a reason for this, and it's all in your data. This will occur when you have data in the sales table, but no corresponding data in the Orders table.

Likewise, I have entries in the traderid, product and total ordered fields but NULLS in productid and sold. SHould the product field not be the same as the productid field.

I assumed that the ProductId and the Product columns contained the same data, which is why I suggested that you add a join condition for those.

This query is using a FULL join, which means... return data from either table and match them up where you can. If there is data in sales, but not order, the sales columns will show the data and the order columns will be null. Likewise, if you have data in orders but not sales, the order columns will have data and the sales columns will be null.

In a case like this, I would suggest that you use the coalesce function. Coalesce accepts many parameters and will return the first one that is not NULL. Given that TraderId and Product represent the same thing, I would coalesce the values from both tables, and then coalesce the total columns with 0 so that you get 0's for your missing data, like this:

Select [!]Coalesce([/!]a.TraderID[!], b.TraderId) As TraderId[/!], 
       [!]Coalesce([/!]a.Product[!],B.ProductId) As ProductId[/!],
       [!]Coalesce([/!]a.TotalOrdered[!], 0) As TotalOrdered[/!],
       [!]Coalesce([/!]b.TotalSold[!], 0) As TotalSold[/!]
From   (
       SELECT dbo.Orders.TraderID, 
              dbo.OrderDetails.Product, 
              SUM(dbo.OrderDetails.Quantity) AS TotalOrdered 
       FROM   dbo.OrderDetails 
              INNER JOIN dbo.Orders 
                 ON dbo.OrderDetails.OrderID = dbo.Orders.OrderID 
       GROUP BY dbo.Orders.TraderID, dbo.OrderDetails.Product
       ) as a 
       FULL OUTER JOIN (
          SELECT dbo.Sales.TraderID, 
                 dbo.SalesDetails.ProductID, 
                 SUM(dbo.SalesDetails.Quantity) AS TotalSold 
          FROM   dbo.Sales 
                 LEFT OUTER JOIN dbo.SalesDetails 
                    ON dbo.Sales.SalesID = dbo.SalesDetails.SalesID 
          GROUP BY dbo.Sales.TraderID, dbo.SalesDetails.ProductID
          ) as b 
          ON  a.TraderID = b.TraderID  
          And a.Product = b.ProductId

With these changes, I guarantee that you will not get any NULL's in your output.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[primagic]

Once again, thank you gmmastros. Your last solution works perfect. Exactly what I was after.