The Use of Union

mille 126 Reputation points
2022-04-14T11:08:57.733+00:00

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'

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,073 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,579 questions
0 comments No comments
{count} votes

Accepted answer
  1. Olaf Helper 42,386 Reputation points
    2022-04-14T11:12:16.017+00:00

    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
    

2 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2022-04-14T18:28:04+00:00

    You also want to use UNION ALL instead of UNION.

    1 person found this answer helpful.
    0 comments No comments

  2. Erland Sommarskog 103.5K Reputation points MVP
    2022-04-14T21:35:36.26+00:00

    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.