Adding Sales Totals from multiple tables

jkirkerx

I'm trying to write better TSQL to reduce the amount of functions I have to do things. Here I'm trying to add the total amount of sales from a column called GrandTotal from multiple tables into a single result. First I'm not sure if this is even possible to do, but I think it can be done. I've come this far, but I get 2 results returned. I'm just trying to consolidate them into one. I tried SELECT SUM(TotalAmount) as the wrapper, but I get a syntax error I tried UNION ALL without the )( around around it, but I get an error on the last ) The tables are identical in columns

DECLARE @StartDate AS Date;
DECLARE @StopDate AS Date;
SET @StartDate = CONVERT(CHAR(10),GETDATE(),101);
SET @StopDate = CONVERT(CHAR(10),DATEADD(d,1, GETDATE()),101);

SELECT
(
SELECT SUM(GrandTotal)
FROM CompletedOrdersHistory
WHERE OrderStatus='COMPLETED'
AND OrderDate >= @StartDate
AND OrderDate < @StopDate
)
UNION ALL
(
SELECT SUM(GrandTotal)
FROM CompletedOrders
WHERE OrderStatus='COMPLETED'
AND OrderDate >= @StartDate
AND OrderDate < @StopDate
)

Mycroft Holmes

Try - note that I named the sum fields.

Select sum(GTotal) Grand
From(
SELECT
(
SELECT SUM(GrandTotal) as GTotal
FROM CompletedOrdersHistory
WHERE OrderStatus='COMPLETED'
AND OrderDate >= @StartDate
AND OrderDate < @StopDate
)
UNION ALL
(
SELECT SUM(GrandTotal) as GTotal
FROM CompletedOrders
WHERE OrderStatus='COMPLETED'
AND OrderDate >= @StartDate
AND OrderDate < @StopDate
))

Personally I would normally include the common ID and use a Group By and then an inner join to where you need the Grand Totals

Never underestimate the power of human stupidity RAH

jkirkerx

Thanks! I understand the example, but I still get the ")" error in which I don't understand. There's only 3 of them. The common ID and the GroupBy with the inner join is going to take me some time to experiment with. I'm not really sure where to start with that one.

Jorgen Andersson

Try to turn it into a CTE and see if that works:

WITH sums AS (
SELECT SUM(GrandTotal) GrandTotal
FROM CompletedOrdersHistory
WHERE OrderStatus='COMPLETED'
AND OrderDate >= @StartDate
AND OrderDate < @StopDate
UNION ALL
SELECT SUM(GrandTotal) GrandTotal
FROM CompletedOrders
WHERE OrderStatus='COMPLETED'
AND OrderDate >= @StartDate
AND OrderDate < @StopDate
)
SELECT Sum(GrandTotal) AS GrandTotal
FROM sums

Or use a subquery which is the same thing but different syntax.

Wrong is evil and must be defeated. - Jeff Ello[^]

jkirkerx

Common Table Expression Yet another way to build more complex queries, Interesting I had no idea I could express it like that, and would of never thought of looking for it in search. I was close on the first couple of tries. Thanks Jorgen! I did read up on those articles you gave me, and have been designing my new stuff in such a manner. Writing TSQL for reports can get more complex to produce the desired results.

Jorgen Andersson

Glad to be able to help. :) Appreciative feed back is the best driving force there is!

Wrong is evil and must be defeated. - Jeff Ello[^]