The Use of Union

Question

SQL query a field summary, when using SUM and UNION, there will be two groups of data, can you merge the two groups of data into one group display?
Below is My code：

SELECT
SUM(19007) AS[Quantity shipped]
FROM 19AX00
WHERE 19011>='2022-1-1' AND 19011<='2022-3-31'
union
SELECT
SUM(23007) AS[Quantity shipped]
FROM 23AX00
WHERE 23011>='2022-1-1' AND 23011<='2022-3-31'

Answer 1

If I got you right to get the total sum, then use your base query as sub-query (derived table) like

SELECT SUM(SubQuery.[Quantity shipped]) AS [Quantity shipped]
FROM
   (SELECT
    SUM(19007) AS[Quantity shipped]
    FROM 19AX00
    WHERE 19011>='2022-1-1' AND 19011<='2022-3-31'
    union
    SELECT
    SUM(23007) AS[Quantity shipped]
    FROM 23AX00
    WHERE 23011>='2022-1-1' AND 23011<='2022-3-31') AS SubQuery

Answer 2

You also want to use UNION ALL instead of UNION.

Answer 3

Here is an improvement of Olaf's query:

 SELECT SUM(SubQuery.[Quantity shipped]) AS [Quantity shipped]
 FROM
    (SELECT 19007 AS [Quantity shipped]
     FROM 19AX00
     WHERE 19011>='2022-1-1' AND 19011<='2022-3-31'
     union ALL
     SELECT 23007
     FROM 23AX00
     WHERE 23011>='2022-1-1' AND 23011<='2022-3-31') AS SubQuery

First of all, as Tom points out, we need UNION ALL here. UNION will remove duplicates which could lead to incorrect results.

Next I have removed the SUM from the inner query, as there is not really any point with it.