Sum of Sum and TOP (n) 2

[LT2]

Using SQL Svr 2005

How do I get a sum of a sum and then, Top 2?

[tt]
Client Provider Date Code Units PdAmt
Charlie A&W 3/7/2009 123 1 4.00
Charlie A&W 3/8/2009 123 1 4.00
Charlie A&W 3/9/2009 246 1 5.00
Charlie A&W 3/10/2009 555 1 7.50
Woodie B&R 4/1/2009 123 1 4.00
Woodie B&R 4/2/2009 246 1 5.00
Snoops CWW 3/21/2009 123 1 4.00
Snoops CWW 3/22/2009 123 1 4.00
Snoops CWW 3/23/2009 123 1 4.00
Snoops CWW 3/24/2009 123 1 4.00
Snoops CWW 3/25/2009 123 1 4.00
Snoops CWW 3/26/2009 246 1 5.00

[/tt]


My results from sub query:
[tt]
Client TotalPd TotalUnits Provider
Charlie 8.00 2 A&W
Charlie 5.00 1 A&W
Charlie 7.50 1 A&W
Woodie 4.00 1 B&R
Woodie 5.00 1 B&R
Snoops 20.00 5 CWW
Snoops 5.00 1 CWW
[/tt]


What I'd like to see:
[tt]
Client TotalPd TotalUnits Provider
Charlie 20.50 4 A&W
Woodie 9.00 2 B&R
Snoops 25.00 6 CWW
[/tt]


And then the Ultimate result (Top 2 desc)
[tt]
Client TotalPd TotalUnits Provider
Snoops 25.00 6 CWW
Charlie 20.50 4 A&W
[/tt]


Simplified version of my query:

SELECT TOP 2 b.Client, b.Cost, b.Units, b.Provider
FROM
(
  Select a.Client, a.ID, sum(a.PdAmt) as 'TotalPd', sum(a.Units) as 'TotalUnits', a.Provider
  From (
         Select c.ClientID as 'ID', (d.LName+ ','+ ' '+ d.FName) as 'Client, p.ProvName as 'Provider',
          t.TxDate, t.TxCode, t.Units, bd.PdAmt
         From tClient c
             inner join tProvider p on c.ProvNum = p.ProvNum
             inner join tTX t on c.ClientNum = t.ClientNum
             left join tBill bd on t.TxID = bd.TxID
         Where p.Test = 0  --No test agency
             and t.TxDate between '3/1/2009' and '4/30/2009'
             and bd.Status = 2 --Paid
             and bd.Adj = 1 --Adjudicated
         )a
   Group by a.Client, a.ID, a.TotalPd, a.TotalUnits, a.Provider
   )b
ORDER BY b.TotalPd desc

Because my results of Table 2 aren't totally summarized like Table 3, my results of TOP 2 are incorrect
as it grabs the largest one summary for the client instead of the largest sum of sum for the client.

How can I fix my syntax to get what I need?

LT

 

[markros]

Just wondering if you ever tried what I posted?

Thanks.