'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 |
|---|
