Total column in Union

1

I did a calculation in the first select statement now I want to total the columns from that calculation in the second select statement. I try to use the alias but it tells me that the column is not valid. Any ideas? SQL follows:

SELECT
CAST(Orders.OrderID AS VARCHAR) AS [OrderID],
Orders.ShipCompanyName,
Orders.ShipFirstName,
Orders.ShipLastName,
Orders.Shipaddress1,
Orders.ShipAddress2,
Orders.ShipCity,
Orders.ShipState,
Orders.ShipPostalCode,
Orders.ShipCountry,
OrderDetails.ProductCode,
OrderDetails.ProductPrice,
OrderDetails.Quantity,
CAST((OrderDetails.ProductPrice * OrderDetails.Quantity) AS MONEY) AS [Total Product Price]

FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

WHERE (OrderDetails.ProductPrice <> 0) AND (Orders.SalesTax1 <> 0) AND (OrderDetails.Quantity <> 0) AND (Orders.ShipDate NOT LIKE 'Null') AND (month(Orders.ShipDate)=5) AND (year(Orders.ShipDate)=2016)

UNION

SELECT
CAST(Orders.OrderID AS VARCHAR) + ' Total',
' ' ,
' ',
' ',
' ',
' ',
' ',
' ',
' ',
' ',
' ',
CAST(SUM(OrderDetails.ProductPrice) AS MONEY),
' ',
SUM([Total Product Price])

FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

WHERE (OrderDetails.ProductPrice <> 0) AND (Orders.SalesTax1 <> 0) AND (OrderDetails.Quantity <> 0) AND (Orders.ShipDate NOT LIKE 'Null') AND (month(Orders.ShipDate)=5) AND (year(Orders.ShipDate)=2016)

GROUP BY CAST(Orders.OrderID AS VARCHAR)

ORDER BY CAST(Orders.OrderID AS VARCHAR)

and this is what the output looks like right now:

sampleQuery.jpg1760×187 75.3 KB

Thank you all for your help. I learn more through this forum than I ever did formally.

John.

2

This is completely untested. Without your schema I'd just be guessing...
I also made a couple of suggestions for the UNION and the CASTs:[code];with RawData
as (
SELECT
CAST(Orders.OrderID AS VARCHAR) AS [OrderID],
Orders.ShipCompanyName,
Orders.ShipFirstName,
Orders.ShipLastName,
Orders.Shipaddress1,
Orders.ShipAddress2,
Orders.ShipCity,
Orders.ShipState,
Orders.ShipPostalCode,
Orders.ShipCountry,
OrderDetails.ProductCode,
OrderDetails.ProductPrice,
OrderDetails.Quantity,
CAST((OrderDetails.ProductPrice * OrderDetails.Quantity) AS MONEY) AS [Total Product Price]
FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
WHERE (OrderDetails.ProductPrice <> 0)
AND (Orders.SalesTax1 <> 0)
AND (OrderDetails.Quantity <> 0)
AND (Orders.ShipDate NOT LIKE 'Null')
AND (month(Orders.ShipDate)=5) AND (year(Orders.ShipDate)=2016)
)
select
rd.*
from RawData rd

UNION -- UNION ALL ???

SELECT
CAST(rd2.OrderID AS VARCHAR) + ' Total',
' ' ,
' ',
' ',
' ',
' ',
' ',
' ',
' ',
' ',
' ',
CAST(SUM(rd.ProductPrice) AS MONEY),
' ',
SUM([Total Product Price])
from RawData rd2
GROUP BY CAST(rd2.OrderID AS VARCHAR) -- Defaults to varchar(30) ???
ORDER BY CAST(rd2.OrderID AS VARCHAR) -- Defaults to varchar(30) ???[/code]

3

Hi Stephen,

Thank you for your suggestions. I made the changes and I get the following error when I run the query:

"Command text was not set for the command object"

I tried googling it, but was not able to figure out what the cause may be.

Thank you.