Group By Problem - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Group By Problem

Author

Topic

ronnyr
Starting Member

21 Posts

Posted - 2006-10-11 : 20:41:10

Hi All,

I have two table, Order and OrderPayment, and the schema looks like this:

Order
------------
OrderID,
InvoiceDT,
InvoiceTotal

OrderPayment
-------------------------
PaymentID
OrderID
PaymentAmount
PaymentDT

Sample Data:

OrderID InvoiceDT InvoiceTotal
-------------------------------------------
1 09/02/2006 5000
2 09/04/2006 4000

PaymentID OrderID PaymentAmount PaymentDT
------------------------------------------------------------
1 1 500 NULL
2 1 500 09/03/2006
3 2 300 09/08/2006

And I want to get all the order with Invoice DT Between certain date. Here is my query:

SELECT

o.OrderID,

o.InvoiceDT, o.InvoiceTotal,

op.PaymentAmount

FROM

[Order] o

LEFT JOIN [OrderPayment] op ON op.OrderID = o.OrderID

WHERE

o.InvoiceDT IS NOT NULL AND

(o.InvoiceDT BETWEEN '09/01/2006' AND '09/20/2006')

But I get two results with the same orderID, eventhough I use GROUP BY

Any idea...

Ron.

“Know where to find the information and how to use it - That's the secret of success”

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-11 : 22:33:36

The query you posted doesn't have a GROUP BY. If you want help with a query, you should post that query.

select o.OrderID, o.InvoiceDT, o.InvoiceTotal, sum(op.PaymentAmount)
...
GROUP BY o.OrderID, o.InvoiceDT, o.InvoiceTotal

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources