How to more grouped a Group by result.

Question

Can any one  help me please.

when i  executed this query

SELECT 
E.NAME
 ,SUM(S.AMOUNT) AS  AMOUNT
 ,Z.ZONE
, COUNT(S.UNITSOLD) AS TOTALCOUNT
FROM ZONE1 Z
INNER JOIN EMPLOYEE1 E
ON Z.ID=E.ZONEID
INNER JOIN SALES1 S
ON S.EMPID=E.ID
INNER JOIN PRODUCT1 P
ON P.ID=S.PRODUCT_ID
GROUP BY 
E.NAME,
Z.ZONE
ORDER BY SUM(S.AMOUNT) DESC

I am getting Grouped Result as following..

NAME    AMOUNT        ZONE
RAJAKURU   350000            EAST   3
BALA           340000                 SOUTH 3
RAHEEM   270000                 SOUTH 3
AKASH   270000                 SOUTH 4
KIRUBA   240000                 NORTH 2
ABDUL   210000             NORTH 3
JAVEED   200000                 EAST  3
SIVA           200000                 NORTH 2
FARHAN   180000                 WEST 2
KABILAN   160000            EAST  2
KAVI           160000           EAST  3
RAHUMAN   160000                SOUTH 2
PRAVEEN   150000                WEST 2
VIJAY   140000                NORTH 2
VEERA   130000                WEST 2
AVINASH   90000                WEST 2 

Now i want that who one is the top amount seller from every zone. Now this is giving 16 Records. But i want

Just 4 Records only.

Ex:-

from NORTH only one record that Who is having maximum amount.

from SOUTH only one record that Who is having maximum amount.

from EAST only one record that Who is having maximum amount.

from WEST only one record that Who is having maximum amount.

THANKS IN ADVANCE.........

Answer 1

;With cteSummed As
(SELECT 
E.NAME
 ,SUM(S.AMOUNT) AS  AMOUNT
 ,Z.ZONE
, COUNT(S.UNITSOLD) AS TOTALCOUNT
FROM ZONE1 Z
INNER JOIN EMPLOYEE1 E
ON Z.ID=E.ZONEID
INNER JOIN SALES1 S
ON S.EMPID=E.ID
INNER JOIN PRODUCT1 P
ON P.ID=S.PRODUCT_ID
GROUP BY 
E.NAME,
Z.ZONE),
cteOrdered As 
(Select NAME, AMOUNT, ZONE, TOTALCOUNT,
   Row_Number() Over(Partition By ZONE, Order By AMOUNT Desc) As rn
From cteSummed)
Select NAME, AMOUNT, ZONE, TOTALCOUNT
From cteOrdered
Where rn = 1;

In the above code, if there is a tie for the largest amount in a ZONE the select will pick one of the ties, it's not determined which one.  If you have a particular one you would want picked, just add additional things to the ORDER BY clause to make the one you want come first, for example, if you wanted the one that came first alphabetically, you would do Order By AMOUNT Desc, NAME.

If in the event there is a tie for the largest amount in a ZONE, you want to return all of the tied rows for that ZONE, then just change Row_Number() to Rank().

Tom

Answer 2

with Cte_A as (SELECT row_number() over (partition by z.zone order by (amount ) desc)) R,
E.NAME
 ,SUM(S.AMOUNT) AS  AMOUNT
 ,Z.ZONE
, COUNT(S.UNITSOLD) AS TOTALCOUNT
FROM ZONE1 Z
INNER JOIN EMPLOYEE1 E
ON Z.ID=E.ZONEID
INNER JOIN SALES1 S
ON S.EMPID=E.ID
INNER JOIN PRODUCT1 P
ON P.ID=S.PRODUCT_ID
GROUP BY 
E.NAME,
Z.ZONE)

select * from Cte_A where R=1

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Dia Agha .

Answer 3

Why you're joining with the Product table if you're not using it?

Also, are you sure your SUM results are correct? The number of joins is a bit frightening.

See this blog post

Aggregates with multiple tables

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog