Answers for "Insert using Union between two tables by one group"
https://ask.sqlservercentral.com/questions/135941/insert-using-union-between-two-tables-by-one-group.html
The latest answers for the question "Insert using Union between two tables by one group"Answer by Gazz
https://ask.sqlservercentral.com/answers/135945/view.html
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
)Thu, 31 Dec 2015 09:11:22 GMTGazzAnswer by Dave_Green
https://ask.sqlservercentral.com/answers/135943/view.html
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.Thu, 31 Dec 2015 09:06:11 GMTDave_Green