469,360 Members | 2,229 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,360 developers. It's quick & easy.

SQL Query - grouping?

senojeel
I just need a push in the right direction.

I querying a MSSQL 2000 Database. I have couple of tables, one is Organizations and the other is Installments. What they do is, the Organizations is obviously org info. The installments is a table that has payments paid out to the organizations. What I want is to query the Organizations and have in the same result row all payments for that org.

For Example.
Organizations table - OrgId, OrgName
Installments - Installment Id, OrgId, NumberPayments, PaymentAmount

I want the results to be
OrgID, OrgName, NumberPayments, PaymentAmount1, PaymentAmount2

Does that make sense?

BTW, if it makes it any easier, there are always going to be only 2 payments.

Thanks!
Shawn
Aug 15 '07 #1
4 986
azimmer
200 Expert 100+
I just need a push in the right direction.

I querying a MSSQL 2000 Database. I have couple of tables, one is Organizations and the other is Installments. What they do is, the Organizations is obviously org info. The installments is a table that has payments paid out to the organizations. What I want is to query the Organizations and have in the same result row all payments for that org.

For Example.
Organizations table - OrgId, OrgName
Installments - Installment Id, OrgId, NumberPayments, PaymentAmount

I want the results to be
OrgID, OrgName, NumberPayments, PaymentAmount1, PaymentAmount2

Does that make sense?

BTW, if it makes it any easier, there are always going to be only 2 payments.

Thanks!
Shawn
If there are no more than two payments:
Expand|Select|Wrap|Line Numbers
  1. select o.OrgID, o.OrgName, (select count(*) from Installments i where i.OrgID=o.orgID) as NumberPayments, p1.PaymentAmount, p2.PaymentAmount
  2. from Organizations o, Installments p1, Installments p2
  3. where o.OrgID=p1.OrgID and o.OrgID=p2.OrgID and p1.[Installment ID]<>p2.[Installment ID]
  4.  
BTW: What is NumberPayment in the Installments table?
Aug 16 '07 #2
If there are no more than two payments:
Expand|Select|Wrap|Line Numbers
  1. select o.OrgID, o.OrgName, (select count(*) from Installments i where i.OrgID=o.orgID) as NumberPayments, p1.PaymentAmount, p2.PaymentAmount
  2. from Organizations o, Installments p1, Installments p2
  3. where o.OrgID=p1.OrgID and o.OrgID=p2.OrgID and p1.[Installment ID]<>p2.[Installment ID]
  4.  
BTW: What is NumberPayment in the Installments table?
NumberPayment is just which payment it is, first or second, which I guess I wouldn't need if we are counting the number of payments already with this query.

Thanks, I will give this a try.

Shawn
Aug 16 '07 #3
NumberPayment is just which payment it is, first or second, which I guess I wouldn't need if we are counting the number of payments already with this query.

Thanks, I will give this a try.

Shawn
Okay. The query is returning either 4 or 8 rows for each organization, where it should be one. The NumberPayments is correct (2) for all rows of the org. The p1.PaymountAmount is correct on all 4 or 8 rows and the p2.PaymentAmount is never right (The number there doesn't make sense at all)

FYI, Each Installment has its own row in the InstallmentsOrg table.

Here is my query. I changed a few things.

Expand|Select|Wrap|Line Numbers
  1. select o.OrgId, o.OrgName, 
  2.     (select count(*)
  3.         from InstallmentsOrg i 
  4.         where i.OrgID=o.orgID AND YEAR(i.DateCreated) = 2007 AND i.InstallmentType = 'RAP') as NumberPayments,
  5.     p1.InstallmentAmt as Payment1,
  6.     p2.InstallmentAmt as Payment2,
  7.     p1.DateCreated,
  8.     p1.InstallmentType
  9. from Organizations o, InstallmentsOrg p1, InstallmentsOrg p2
  10. where o.OrgID=p1.OrgId and o.OrgID=p2.OrgId and p1.InstallmentId<>p2.InstallmentId AND YEAR(p1.DateCreated) = 2007 AND p1.InstallmentType = 'RAP'
Aug 16 '07 #4
So I think I might be closer?

This probably isn't the best way to do it, but I am getting closer on my results.

Here is my query:
Expand|Select|Wrap|Line Numbers
  1. select 
  2.     i.OrgId, 
  3.     o.OrgName, 
  4.     i1.InstallmentAmt as Installment1, 
  5.     i2.InstallmentAmt as Installment2,
  6.     min(i.InstallmentId) as InstallmentId_1, 
  7.     max(i.InstallmentId) as InstallmentId_2
  8. FROM InstallmentsOrg i
  9. LEFT JOIN Organizations o ON i.OrgId = o.OrgId
  10. Left Join InstallmentsOrg i1 ON i.InstallmentId = i1.InstallmentId
  11. Left Join InstallmentsOrg i2 ON i.InstallmentId = i2.InstallmentId
  12.  
  13. WHERE i.InstallmentType = 'RAP' and YEAR(i.DateCreated) = 2007
  14.  
  15. GROUP BY i.OrgId,o.OrgName,i1.InstallmentAmt, i2.InstallmentAmt
  16.  
  17. order by o.OrgName 
  18.  
My problem now is I can't get Installment2 to equal the 2nd Installment that was the ID of InstallmentId_2

Make sense?
Aug 16 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Debbie Davis | last post: by
9 posts views Thread by cyrus.kapadia | last post: by
4 posts views Thread by Orion | last post: by
7 posts views Thread by jane | last post: by
4 posts views Thread by Philippe | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.