Arranging the select statement in order

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

SQL

SELECT COA.acc_code  AS  'Account Code', COA.acc_name  AS  'Account Name', CAST(SUM(debit-credit) AS varchar) AS [$ Debit]
FROM COA  JOIN AccountReceivable
ON COA.acc_name = AccountReceivable.acc_name
WHERE COA.acc_name IN ('Account Receivable')  
GROUP BY COA .acc_code, COA.acc_name

union all

SELECT COA.acc_code  AS  'Account Code', COA.acc_name  AS  'Account Name', CAST(SUM(debit-credit) AS varchar) AS [$ Credit]
FROM COA  JOIN AccountPayable
ON COA.acc_name = AccountPayable.acc_name
WHERE COA.acc_name IN ('Account Payable')  
GROUP BY COA .acc_code, COA.acc_name

I want my result output be like the second table where debit value is under debit credit value is under credit.

Account Code	    Account Name	                Debit
101	            Account Receivable	                200	
200	            Account Payable		       -250

Account Code	    Account Name	                Debit	                Credit
101	            Account Receivable	                               200	
200	            Account Payable		                                           -250

[edit]Code block added - OriginalGriff[/edit]

Posted 9-Apr-13 2:35am

Mathiudi

Updated 9-Apr-13 2:39am

OriginalGriff

v3

Add a Solution

Comments

ZurdoDev 9-Apr-13 8:49am

I'm really confused. Are you saying that you want the order of your fields to be different? If so, then change them. What's the issue?

Sergey Alexandrovich Kryukov 9-Apr-13 11:32am

Please do yourself a great favor: stop posting non-answers as "solutions" and non-questions as "questions".
—SA

2 solutions

Solution 2

Try this

SELECT COA.acc_code AS 'Account Code', COA.acc_name AS 'Account Name', CAST(SUM(debit-credit) AS varchar) AS [$ Debit],CAST(SUM(debit-credit) AS varchar) AS [$ Credit]
FROM COA left outer JOIN AccountReceivable
ON COA.acc_name = AccountReceivable.acc_name
left outer join AccountPayable
on COA.acc_name = AccountPayable.acc_name
WHERE COA.acc_name IN ('Account Receivable', 'Account Payable')
GROUP BY COA .acc_code, COA.acc_name

Posted 9-Apr-13 3:03am

rafeek_asharaf

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Accepted Answer · 2013-04-09T02:59:00

You cannot, in an UNION statement, give two different names to a column (here I'm talking about the [$ Debit]/[$ Credit] columns). Moreover, you only have to specify column names in the first query (all subsequent queries will inherit the naming schema).

You have to select 4 columns ; in the first query give the [$ Credit] a null value ; in the second one, give the [$ Debit] a null value.

Thus:

SQL

SELECT
   COA.acc_code AS [Account Code]
  ,COA.acc_name AS [Account Name]
  ,CAST(SUM(debit-credit) AS varchar) AS [$ Debit]
  ,NULL AS [$ Credit]
FROM
  COA JOIN AccountReceivable ON COA.acc_name = AccountReceivable.acc_name
WHERE
  COA.acc_name IN ('Account Receivable')
GROUP BY
  COA.acc_code, COA.acc_name
 
UNION ALL
 
SELECT
   COA.acc_code
  ,COA.acc_name
  ,NULL
  ,CAST(SUM(debit-credit) AS varchar)
FROM
  COA JOIN AccountPayable ON COA.acc_name = AccountPayable.acc_name
WHERE
  COA.acc_name IN ('Account Payable')
GROUP BY
  COA.acc_code, COA.acc_name

Hope this helps.

Arranging the select statement in order

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Arranging the select statement in order

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us