Framing query - 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

Framing query

Author

Topic

magdoomak
Starting Member

3 Posts

Posted - 2010-08-15 : 06:35:51

I have master and detailed table and as follows

Master: jv_date,jv_no,batch_code

Detail: jv_date,jv_no,debit_or_credit,amount
Values in Detail table are:

JV_DATE JV_NO DEBIT_OR_CREDIT AMOUNT
10-MAY-2010 10101 D 500.00
10-MAY-2010 10101 D 300.00
10-MAY-2010 10101 D 200.00
10-MAY-2010 10101 C 1000.00

10-MAY-2010 10102 D 1200.00
10-MAY-2010 10102 C 300.00
10-MAY-2010 10102 C 200.00
10-MAY-2010 10102 C 700.00

The output should be like below
10-MAY-2010 10101 D 1000.00
10-MAY-2010 10101 C 1000.00

10-MAY-2010 10102 D 1200.00
10-MAY-2010 10102 C 1200.00

Thanks in advance.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-15 : 06:48:25

select
JV_DATE,
JV_NO,
DEBIT_OR_CREDIT,
sum(AMOUNT) as amount
from Detailtable
group by JV_DATE, JV_NO, DEBIT_OR_CREDIT

No, you're never too old to Yak'n'Roll if you're too young to die.

magdoomak
Starting Member

3 Posts

Posted - 2010-08-15 : 07:54:41

Thanks for reply.

But the problem is, in the detail table, amount goes to different account for the same jv number. It is as below:

JV_DATE JV_NO DEBIT_OR_CREDIT ACCOUNT AMOUNT
10-MAY-2010 10101 D 10001 500.00
10-MAY-2010 10101 D 10002 300.00
10-MAY-2010 10101 D 10003 200.00
10-MAY-2010 10101 C 10004 1000.00

JV_DATE JV_NO DEBIT_OR_CREDIT ACCOUNT AMOUNT
10-MAY-2010 10102 C 10001 500.00
10-MAY-2010 10102 C 10002 300.00
10-MAY-2010 10102 C 10003 200.00
10-MAY-2010 10102 D 10004 1000.00

How can we write this query to sum the amount by debit or credit, so that at the end I will get only one debit and credit for each jv no.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-15 : 08:01:12

My given solution isn't grouping by ACCOUNT because you havn't posted it in your first post.
So the query should exactly bring what you want.

Did you try?

No, you're never too old to Yak'n'Roll if you're too young to die.

magdoomak
Starting Member

3 Posts

Posted - 2010-08-15 : 08:20:40

I forgot to add this coloumn in my first post.

Yes. I tried, it works fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources