Help finding group totals for year end

My SQLServer 2k database has the following tables:
Transaction
Customer
Employee
Every Transaction is assigned a customerId
Every Customer is assigned an employee id
All Transactions have a total
What I am trying to do is calculate the top 5 customers by total for each
employee
I have the following which gives me the top 5 customers, but how do I get
the top 5 for each employee?
WB
Syntax:
SELECT t.CustomerId, sum(t.total)
FROM tblTransaction t
WHERE Year(t.TransactionDate) = 2005
GROUP BY t.CustomerId
ORDER BY sum(t.total) DESCSELECT t.employeeid, sum(t.total)
FROM tblTransaction t INNER JOIN employee e
on t.customerid = e.customerid
WHERE Year(t.TransactionDate) = 2005
GROUP BY t.employeeid
ORDER BY sum(t.total) DESC
"WB" wrote:

> My SQLServer 2k database has the following tables:
> Transaction
> Customer
> Employee
> Every Transaction is assigned a customerId
> Every Customer is assigned an employee id
> All Transactions have a total
> What I am trying to do is calculate the top 5 customers by total for each
> employee
> I have the following which gives me the top 5 customers, but how do I get
> the top 5 for each employee?
>
> WB
> Syntax:
> SELECT t.CustomerId, sum(t.total)
> FROM tblTransaction t
> WHERE Year(t.TransactionDate) = 2005
> GROUP BY t.CustomerId
> ORDER BY sum(t.total) DESC
>
>|||Is there any uniqueness about the TransAction table like an IDENTITY
Column..? I've assumed there is :-
select * from tblTransAction t1 WHERE PK_TranID IN
(SELECT TOP 5 t2.PK_TranID
FROM tblTransAction as t2
WHERE t2.CustomerID = t1.CustomerID
GROUP BY t2.CustomerID, t2.TransactionID, t2.Total
ORDER BY SUM(t2.Total) DESC)
HTH. Ryan
"WB" <none> wrote in message news:eiqpObVFGHA.376@.TK2MSFTNGP12.phx.gbl...
> My SQLServer 2k database has the following tables:
> Transaction
> Customer
> Employee
> Every Transaction is assigned a customerId
> Every Customer is assigned an employee id
> All Transactions have a total
> What I am trying to do is calculate the top 5 customers by total for each
> employee
> I have the following which gives me the top 5 customers, but how do I get
> the top 5 for each employee?
>
> WB
> Syntax:
> SELECT t.CustomerId, sum(t.total)
> FROM tblTransaction t
> WHERE Year(t.TransactionDate) = 2005
> GROUP BY t.CustomerId
> ORDER BY sum(t.total) DESC
>
>