Avoid Duplicates in SQL Server 2005?

I am wrote this SQL:

select distinct PayID, PaymentDate, AccountNumber, sum(PaymentAmount) as Amount 
from tblInvoicePaymentDetails 
where CustomerID = 'mud4978' 
group by PayID, PaymentDate, AccountNumber

Output is:

PayID    PayDate                    Account      Amount
1        2011-07-05 11:09:14.390    NULL       700.00
1        2011-07-05 11:09:14.407    NULL       100.00
2        2011-07-05 11:20:05.517    NULL       0.00
2        2011-07-05 11:20:05.547    9000       500.00
2        2011-07-05 11:20:07.000    9000       100.00
3        2011-07-05 12:19:22.017    100000     200.00
3        2011-07-05 12:19:22.077    100000     100.00

But my requirements are to display 3 records (avoid duplicates) and sum of amount like this:

payid         paydate             account       amount
1           2011-07-05 11:09:14   null     开发者_JAVA技巧     800.00
2           2011-07-05 11:20:05   9000          600.00
3           2011-07-05 12:19:22   100000        300.00

please help

---

It looks like you only want to group by PayID (otherwise other columns will be taken into account).

PayDate varies, so if you want one of those values you need to pick which exact PayDate you want in your output. You may wish to consider Max or Min to pick one that exists already.

Account also varies, so again you need to pick one using something like Min or Max

Your query would then look something like this:

SELECT DISTINCT 
    PayID, 
    MIN(PaymentDate) AS PaymentDate, 
    MAX(AccountNumber) as AccountNumber, 
    SUM(PaymentAmount) as Amount 
FROM tblInvoicePaymentDetails 
WHERE CustomerID='mud4978' 
GROUP BY PayID

---

The problem is that the paydate column contains different values. You need to normalize the values inside so they represent all the same date, for those that shall be grouped. This can be achieved by updating each row, which you might not want to.

You could add another row containing the date only (The time value has to be always 00:00:00 in this case).

The third alternative is to create a subquery containing normalised date values, which I would recommend for the start. You can figure out how to achieve this by looking at my example below. Replace the table variable by your table name in the select statement as well as the column names.

DECLARE @x TABLE (
amount int,
dt DateTime )

INSERT INTO @x (amount, dt) VALUES (1,  '01.01.2005 12:00:12')
INSERT INTO @x (amount, dt) VALUES (2,  '01.01.2005 10:00:12')
INSERT INTO @x (amount, dt) VALUES (2,  '01.02.2005 10:00:12')

SELECT SUM(a), b FROM 
    (
        SELECT 
            amount AS a, 
            DATEADD(dd, 0, DATEDIFF(dd, 0, dt)) AS b
        FROM 
            #x
    ) subtable
GROUP BY b