SQL Server - Get the final sum of a product with two columns

advertisements

Despite my little knwoledge of SQL Server, i'm stucked on this problem for a while:

I'm trying to get the final sum of a product of two columns, but SQL Server won't recognize my last column. Here is the query:

SELECT
Products.customName AS 'Name',
Ordered_Products.scanned AS 'Sent Quantity',
Charged_Products.price AS 'Product Price',
Ordered_Products.scanned * Charged_Products.price AS 'Charged'
FROM Products
    JOIN Charged_Products
        ON Products.productsId = Charged_Products.productsId
    JOIN Ordered_Products
        ON Ordered_Products.productsId = Products.productsId
WHERE
Ordered_Products.ordersId = 500 AND
Ordered_Products.scanned > 0
UNION ALL
SELECT 'TOTAL', '', '', SUM('Charged')

It should look something like this:

Could somebody point me in the right direction to make the query work ? Any help would be much appreciated.


You can do it this way:

SELECT
Products.customName AS 'Name',
Ordered_Products.scanned AS 'Sent Quantity',
Charged_Products.price AS 'Product Price',
Ordered_Products.scanned * Charged_Products.price AS 'Charged'
FROM Products
    JOIN Charged_Products
        ON Products.productsId = Charged_Products.productsId
    JOIN Ordered_Products
        ON Ordered_Products.productsId = Products.productsId
WHERE
Ordered_Products.ordersId = 500 AND
Ordered_Products.scanned > 0

UNION ALL

SELECT 'TOTAL', '', '', SUM(Ordered_Products.scanned * Charged_Products.price)
FROM Products
    JOIN Charged_Products
        ON Products.productsId = Charged_Products.productsId
    JOIN Ordered_Products
        ON Ordered_Products.productsId = Products.productsId
WHERE
Ordered_Products.ordersId = 500 AND
Ordered_Products.scanned > 0