Vitalie Dogotari Vitalie Dogotari - 1 month ago 14
SQL Question

How to UNION 2 CTE results in SQL

--UNION gives me error

;WITH TopSelling
AS
(
SELECT p.Name AS ProductName,SUM(LineTotal) AS TotalAmount
FROM Product AS p INNER JOIN SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
GROUP BY p.Name
)


SELECT ProductName, TotalAmount
FROM TopSelling
ORDER BY TotalAmount DESC
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY


UNION ALL

SELECT 'Grand Total' AS ProductName, sum(TotalAmount) AS TotalAmount
FROM TopSelling

Answer
    ;WITH TopSelling 
    AS
    (
    SELECT p.Name AS ProductName,SUM(LineTotal) AS TotalAmount
    FROM Product AS p INNER JOIN SalesOrderDetail AS sod
    ON p.ProductID = sod.ProductID
    GROUP BY p.Name
    ),
    topSelling1 as 
    (
    SELECT ProductName, TotalAmount
    FROM TopSelling
    ORDER BY TotalAmount DESC
    OFFSET 0 ROWS
    FETCH NEXT 5 ROWS ONLY
    ),
total as
(
    SELECT sum(TotalAmount) AS TotalAmount
    FROM TopSelling;
)
    select ProductName, TotalAmount
    FROM TopSelling1
    UNION ALL
    SELECT 'Grand Total' AS ProductName, TotalAmount
    FROM Total;