Select union prob - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select union prob

Author

Topic

Lemmor1120
Starting Member

5 Posts

Posted - 2014-02-20 : 17:24:56

have problem..please help

Lemmor1120
Starting Member

5 Posts

Posted - 2014-02-20 : 17:27:41

Hello Everyone,... I am having a problem using SELECT UNION
here is my code:

SELECT acc_code,sum(debit) as sdebit,SUM(credit) as scredit FROM OR_child WHERE [acc_code] IN (SELECT CodeID FROM Codes WHERE CodeType = 'INQMISC') AND doc_date <= '02/22/2014' AND Mem_Code = '13184' GROUP BY Acc_Code
Union
SELECT acc_code,sum(debit) as sdebit,SUM(credit) as scredit FROM JV_child WHERE [acc_code] IN (SELECT CodeID FROM Codes WHERE CodeType = 'INQMISC') AND doc_date <= '02/22/2014' AND Mem_Code = '13184' GROUP BY Acc_Code
Union
SELECT glaccount as acc_code,0 as sdebit,SUM(amount) as scredit FROM glxacts WHERE [glaccount] IN (SELECT CodeID FROM Codes WHERE CodeType = 'INQMISC') AND createdate <= '02/22/2014' AND cardid = '13184' GROUP BY GLAccount

and here is the result of my code:

acc_code sdebit scredit
40160 0.00 -505.00
40160 0.00 445.00
40402 0.00 50.00
40420 0.00 -10.00

some acc_code doesnt add up... whats wrong with my code.. please help...thanks in advance...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-20 : 17:34:19

quote:
Originally posted by Lemmor1120

some acc_code doesnt add up... whats wrong with my code.. please help...thanks in advance...

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

How is it not adding up? It looks like it is given the output you posted.

Lemmor1120
Starting Member

5 Posts

Posted - 2014-02-20 : 17:40:06

Hello,... Kindly check the 40160 acc_code. I wish to combine the results

Lemmor1120
Starting Member

5 Posts

Posted - 2014-02-20 : 17:50:42

I really need help with this code... I'm stuck with it. It will be a big help. Thanks thanks..

oh, and Thanks for the link,.. sorry for the posting... this is my first time right a post in a forum... thanks again.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-20 : 18:11:05

I still don't know what you want as you haven't show us what output you expect. My GUESS is that you want to GROUP by the acc_code:

SELECT
	acc_code,
	SUM(sdebit) AS sdebit,
	SUM(scredit) AS scredit
FROM
(
	SELECT acc_code,sum(debit) as sdebit,SUM(credit) as scredit FROM OR_child WHERE [acc_code] IN (SELECT CodeID FROM Codes WHERE CodeType = 'INQMISC') AND doc_date <= '02/22/2014' AND Mem_Code = '13184' GROUP BY Acc_Code 
	Union 
	SELECT acc_code,sum(debit) as sdebit,SUM(credit) as scredit FROM JV_child WHERE [acc_code] IN (SELECT CodeID FROM Codes WHERE CodeType = 'INQMISC') AND doc_date <= '02/22/2014' AND Mem_Code = '13184' GROUP BY Acc_Code 
	Union 
	SELECT glaccount as acc_code,0 as sdebit,SUM(amount) as scredit FROM glxacts WHERE [glaccount] IN (SELECT CodeID FROM Codes WHERE CodeType = 'INQMISC') AND createdate <= '02/22/2014' AND cardid = '13184' GROUP BY GLAccount
) AS T
GROUP BY
	acc_code

Lemmor1120
Starting Member

5 Posts

Posted - 2014-02-20 : 18:34:51

Thats exactly what I was trying to do... Your a genius Lamprey! thanks so much... this is a nice group. fast and effective response. Hope I can join this group. thanks again... cheers!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources