Convert query from MS Access to SQL Server

Question

I have limited experience with SQL but I have been asked to convert the below query from MS Access.

TRANSFORM SUM(weekpace_crosstab.wgt) AS SumOfwgt
    SELECT 
        Products.[Product Type], SUM(weekpace_crosstab.wgt) AS DeliveryTotal    
    FROM 
        (weekpace_crosstab 
    LEFT JOIN 
        Customer ON weekpace_crosstab.Customer = Customer.Customer)  LEFT JOIN Products ON weekpace_crosstab.Product = Products.[Product Code]
WHERE (((Customer.[Customer Group])="Sainsbury"))
GROUP BY Products.[Product Type]
PIVOT weekpace_crosstab.Date;

This is the result...

Through Google and other questions on this forum I have written the below statement. However, I cannot get the delivery total to appear like in the image above.

SELECT * FROM 
(
SELECT P.[Product Type], wc.Date, sum (wc.wgt) AS DeliveryTotal

FROM weekpace_crosstab AS wc LEFT JOIN Customer AS C ON wc.Customer = C.Customer LEFT JOIN Products as p 

ON wc.Product = P.[Product Code]

WHERE C.[Customer Group]='Co-op'

GROUP BY  p.[Product Type], wc.Date, wc.wgt
) AS s
PIVOT 
(
SUM (DeliveryTotal)
FOR [Date] in ([2017-01-23],[2017-01-24],[2017-01-25],[2017-01-26],[2017-01-27],[2017-01-28],[2017-01-29])
)AS pvt
ORDER BY [Product Type]

Here is the result of this query...

Can anyone advise on how to get the delivery total column added?

Thanks for your attention.

Please add a tag for the form of SQL you are converting to (i.e. MySQL, SQL-Server, etc.). — toonice
– toonice, Commented May 10, 2017 at 11:56

David Waldron · Accepted Answer · 2017-05-10 13:06:51Z

1

You need to add in the DeliveryTotal as a second column, as the first one is being used by the pivot it will show pivoted only

Try

SELECT * FROM 
(
SELECT P.[Product Type], wc.Date, sum (wc.wgt) AS DeliveryTotal, w.wgt
FROM weekpace_crosstab AS wc LEFT JOIN Customer AS C ON wc.Customer = 
C.Customer LEFT JOIN Products as p 

ON wc.Product = P.[Product Code]
LEFT JOIN (
SELECT SUM(wgt)wgt, Product 
FROM weekpace_crosstab 
WHERE Date IN ('2017-01-23', '2017-01-24', '2017-01-25', '2017-01-26', '2017-01-
27', '2017-01-28', '2017-01-29')
GROUP BY product) w ON w.Product = wc.product

WHERE C.[Customer Group]='Co-op'

GROUP BY  p.[Product Type], wc.Date, w.wgt
) AS s
PIVOT 
(
SUM (DeliveryTotal)
FOR [Date] in ([2017-01-23],[2017-01-24],[2017-01-25],[2017-01-26],[2017-01-
27],[2017-01-28],[2017-01-29])
)AS pvt
ORDER BY [Product Type]

edited May 10, 2017 at 13:06

answered May 10, 2017 at 12:15

David Waldron

1627 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Somerville Over a year ago

When i run this I do get the DeliveryTotal column however it has lost the grouping on the product type.

David Waldron Over a year ago

Just made a quick edit, added in a new join which should solve this issue.

Collectives™ on Stack Overflow

Convert query from MS Access to SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related