'How to Group By on Static Data having UNION in SQL Server

I am trying to do a SELECT with UNION in Microsoft SQL Server where SQL statement has static string columns on which I would like to do a Group By. I am getting this error:

[Code: 207, SQL State: S0001] Invalid column name 'chrg_tx'. [Script position: 221 - 237]

The same SQL works in other RDBMS like INFORMIX.

Here is my SQL

SELECT 
    '' AS chrg_tx, 
    SUM(gross_after_discount) gross, 
    SUM(tax) tax, 
    SUM(net) net
FROM 
    invce_line_item 
WHERE 
    ctry_cd = 'US' 
    AND lcl_btch_id = '2021-11-1214:49:20.838.EN'
    AND invce_no = 'INV000001' 
    AND tax != 0 
    AND insr = 0 
GROUP BY 
    chrg_tx

UNION

SELECT 
    'ASSURANCE' AS chrg_tx,
    SUM(insr + insr_dsct) gross,
    SUM(insr_tax) tax, 
    SUM(net_insr) net
FROM 
    invce_line_item 
WHERE 
    ctry_cd = 'US' 
    AND lcl_btch_id = '2021-11-1214:49:20.838.EN'
    AND invce_no = 'INV000001' 
    AND insr != 0 
GROUP BY 
    chrg_tx

What can I try to resolve this?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source