question

Ashraf avatar image
Ashraf asked

Insert using Union between two tables by one group

This is my SQL to execute but it produces the following error: (Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'select'. Msg 102, Level 15, State 1, Line 15 Incorrect syntax near ')'. insert into settlm_AC ( select code_prjts as code_prjts, sum(case when is_open_bln=1 then net_value else 0 end) as netA_o , sum(case when is_open_bln=0 then net_value else 0 end) as netA_c, sum(case when is_open_bln=1 then paid_value else 0 end) as paidA_o, sum(case when is_open_bln=0 then paid_value else 0 end)as paidA_c, sum(case when is_open_bln=1 then tax_sal else 0 end) as taxA_o, sum(case when is_open_bln=0 then tax_sal else 0 end) as taxA_c from settlm_s3 union select code_prjts as code_prjts, sum(case when is_open_bln=1 then net_value_Cut else 0 end) as netC_o, sum(case when is_open_bln=0 then net_value_Cut else 0 end) as netC_c, sum(case when is_open_bln=1 then paid_value_Cut else 0 end) as paidC_o, sum(case when is_open_bln=0 then paid_value_Cut else 0 end) as paidC_c, sum(case when is_open_bln=1 then tax_sal_Cut else 0 end) as taxC_o, sum(case when is_open_bln=0 then tax_sal_Cut else 0 end) as taxC_c from chsttm_s3 ) group by code_prjts
insertaggregatesunion
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Out of interest, is there a reason you're using UNION instead of UNION ALL?
0 Likes 0 ·
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
Dave_Green avatar image
Dave_Green answered
Hi there, it looks like your **group by** may be in the wrong place. You appear to have a query of the format : Insert.. (select xyz from tbl1 union select xyz from tbl2) group by x however, the correct format would be either: Insert.. (select xyz from tbl1 union select xyz from tbl2 group by x) or Insert.. select x,sum(y),sum(z) from (select xyz from tbl1 union select xyz from tbl2) subqry group by x depending whether you wanted to group only the second query or the whole lot.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Gazz avatar image
Gazz answered
INSERT INTO settlm_AC SELECT * FROM ( SELECT code_prjts , SUM(CASE WHEN is_open_bln = 1 THEN net_value ELSE 0 END) , SUM(CASE WHEN is_open_bln = 0 THEN net_value ELSE 0 END) , SUM(CASE WHEN is_open_bln = 1 THEN paid_value ELSE 0 END) , SUM(CASE WHEN is_open_bln = 0 THEN paid_value ELSE 0 END) , SUM(CASE WHEN is_open_bln = 1 THEN tax_sal ELSE 0 END) , SUM(CASE WHEN is_open_bln = 0 THEN tax_sal ELSE 0 END) FROM settlm_s3 UNION SELECT code_prjts , SUM(CASE WHEN is_open_bln = 1 THEN net_value_Cut ELSE 0 END) , SUM(CASE WHEN is_open_bln = 0 THEN net_value_Cut ELSE 0 END) , SUM(CASE WHEN is_open_bln = 1 THEN paid_value_Cut ELSE 0 END) , SUM(CASE WHEN is_open_bln = 0 THEN paid_value_Cut ELSE 0 END) , SUM(CASE WHEN is_open_bln = 1 THEN tax_sal_Cut ELSE 0 END) , SUM(CASE WHEN is_open_bln = 0 THEN tax_sal_Cut ELSE 0 END) FROM chsttm_s3 GROUP BY code_prjts )
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.