July 18, 2012 at 5:57 am
Hello,
I have encountered a situation where I am aggregating a column from two tables via a UNION ALL and have found that I am not getting a combined result.
One table is actually empty (Table Archive.Sales shown in the example below) and when I run the following query:
SELECTSUM(Qty) AS Total
FROM Trans.Sales
UNION ALL
SELECTSUM(Qty)
FROM Archive.Sales;
I get the follwing result set:
Total
-----------
4193
NULL
(2 row(s) affected)
I was thinking about how to modify this query to eliminate the NULL row from the aggregated result, so I modified the above to:
ELECTSUM(SQ1.Total)
FROM
(
SELECTSUM(Qty) AS Total
FROM Trans.Sales
UNION ALL
SELECTSUM(Qty)
FROM Archive.Sales
) AS SQ1;
And got the result I wanted, but with an additional message thrown in (which I don't want to see) - like this:
-----------
4193
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
Any ideas on how I can avoid the above and simply show a combined total regardless of whether one of the two UNION'd tables is empty?
Thanks
Steve
July 18, 2012 at 6:04 am
Wrapping the output column in an ISNULL should resolve the issue.
SELECT
SUM(SQ1.Total)
FROM
(
SELECT
SUM(ISNULL(Qty,0)) AS Total
FROM
Trans.Sales
UNION ALL
SELECT
SUM(ISNULL(Qty,0))
FROM
Archive.Sales
) AS SQ1;
Edit had ISNULL outside of the SUM instead of inside the SUM.
July 18, 2012 at 6:35 am
Perfect! Thanks 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply