quote:

---

Originally posted by Ifor

-- *** Test Data ***
DECLARE @t TABLE
(
    [ID] int NOT NULL
    ,From_Date datetime NOT NULL
    ,Paid_Amt money NOT NULL
    ,Status char(1) NOT NULL
)
INSERT INTO @t
-- With test data best to put dates in ISO format to allow people to test
-- without being concerned about locale settings.
SELECT 5599, '2008-04-23', 60, 'A' UNION ALL
SELECT 9699, '2008-04-11', 40.92, 'A' UNION ALL
SELECT 799, '2008-04-11', 60, 'A' UNION ALL
SELECT 4799, '2008-05-14', 60, 'A' UNION ALL
SELECT 7599, '2008-04-30', 89.57, 'A'
-- *** End Test Data ***

-- Your query
SELECT [ID], From_date, COUNT(From_date) AS CountOfFrom_Date, Paid_Amt
FROM @t
-- Only worth putting aggregate values in HAVING clause
WHERE Status <> 'D'
GROUP BY [ID], From_date, Paid_Amt

-- Normally best to count in the middle tier or front end.
-- If really want an extra column you can use a derived table in SQL2000.
SELECT T1.[ID], T1.From_date, COUNT(T1.From_date) AS CountOfFrom_Date, T1.Paid_Amt, T2.Total
FROM @t T1
    CROSS JOIN
    (
        SELECT COUNT(T21.From_date) AS Total
        FROM @t T21
        WHERE T21.Status <> 'D'
    ) T2
WHERE T1.Status <> 'D'
GROUP BY T1.[ID], T1.From_date, T1.Paid_Amt, T2.Total

-- You may also like to actually count the From_Date
-- (ie Not the combination of ID, From_date and Paid_Amt)
SELECT T1.[ID], T1.From_date, T3.CountOfFrom_Date, T1.Paid_Amt, T2.Total
FROM @t T1
    CROSS JOIN
    (
        SELECT COUNT(T21.From_date) AS Total
        FROM @t T21
        WHERE T21.Status <> 'D'
    ) T2
    JOIN
    (
        SELECT T31.From_date, COUNT(*) AS CountOfFrom_Date
        FROM @t T31
        WHERE T31.Status <> 'D'
        GROUP BY T31.From_date
    ) T3
        ON T1.From_date = T3.From_date
WHERE T1.Status <> 'D'

---