CODEWITHSUNDEEP
sql
marceloasr
marceloasr

Reputation: 375

Query to Show Max Sales for Each Seller

I have a table like this (sample):

Name_Seller   Month   Value
---------------------------
Seller A      Jan     200
Seller B      Jan     100
Seller A      Fev     300
Seller B      Fev     100
Seller C      Jan     400
Seller A      Mar     200
Seller D      Jan     300

SQL query:

SELECT Name_Seller, Month, Value 
FROM SALES
WHERE Value = (SELECT MAX(Value) FROM SALES GROUP BY Name_Seller);

And I'd like to print for each seller which was his maximum sale and when it was.

Could you help me fix my query and explain why it does not work?

I tried:

select name_seller, month, max(value) 
from sales 
group by name_seller, month;

but this query returns:

+---------------+------------+------+
| NAME_SELLER   | MAX(VALUE) | MONTH|
+---------------+------------+------+
| SELLER A      |    4182.00 | Jan  |
| SELLER A      |    3261.00 | Fev  |
| SELLER A      |    4219.00 | Mar  |
| SELLER B      |    2123.00 | Jan  |
| SELLER B      |    2111.00 | Fev  |
| SELLER B      |    3918.00 | Mar  |
| SELLER C      |    3000.00 | Jan  |
| SELLER C      |    4000.00 | Fev  |
| SELLER C      |    1500.00 | Mar  |
| SELLER D      |    2819.00 | Jan  |
| SELLER D      |    3881.00 | Fev  |
| SELLER D      |    2012.00 | Mar  |
+---------------+------------+------+

And I would like just THE TOP sale of each salesman and when it was.

So it should return just one sale for each salesman.

Upvotes: 3

Views: 10243

Answers (4)

Jim Macaulay
Jim Macaulay

Reputation: 5165

You can use below query,

select name_seller, month, max(value) from sales group by name_seller, month;

If you are expecting month as well then use,

select s2.name_seller, s1.month, max(s2.value) from sales s1
inner join 
(select name_seller, max(value) as value from sales
group by name_seller) s2
on (s1.name_seller = s2.name_seller and s1.value = s2.value);

Upvotes: 0

VTi
VTi

Reputation: 1319

You query can also bring the results however the "=" operator in where clause needs to change to "IN" because the query below brings more than 1 row so it needs a IN operator in where clause. Also, the data you have in your query returned the correct results but please be wary to use it in general because it will also bring wrong results because of comparison with sales amount( value) as example given by @forpas.

enter image description here

When changed the operator, your query will work.

 SELECT Name_Seller, Month, Value FROM SALES
 WHERE Value IN (Select MAX(Value) FROM SALES GROUP BY Name_Seller);

You can also use the rank() window function 

 SELECT Name_Seller, Month, VALUE
 FROM (SELECT Name_Seller, Month, VALUE,
       RANK() OVER (PARTITION BY Name_Seller ORDER BY VALUE DESC ) as RN
       FROM SALES
      ) A
 WHERE A.RN = 1

enter image description here

Upvotes: 2

forpas
forpas

Reputation: 164214

With ROW_NUMBER() window function:

SELECT t.Name_Seller, t.Month, t.Value
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Name_Seller ORDER BY Value DESC) rn
  FROM SALES
) t
WHERE t.rn = 1

Change ROW_NUMBER() with RANK() if you want ties returned.

Or with a correlated subquery in the WHERE clause:

SELECT s.* FROM SALES s
WHERE s.Value = (SELECT MAX(VALUE) FROM SALES WHERE Name_Seller = s.Name_Seller)

Or if your database supports it:

SELECT * FROM SALES
WHERE (Name_Seller, Value) IN (SELECT Name_Seller, MAX(VALUE) FROM SALES GROUP BY Name_Seller)

Upvotes: 2

mentamarindo
mentamarindo

Reputation: 647

It will look like this:

SELECT Name_Seller, Month, MAX(Value)
FROM SALES
GROUP BY Name_Seller, Month;

Upvotes: 0

Related Questions