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
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.
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.
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 )
21 People are following this question.
