w3resource

SQL Challenges-1: Salesperson that makes maximum number of sales amount

SQL Challenges-1: Exercise-16 with Solution

From the following table, write a SQL query to find the salesperson that makes maximum number of sales amount.
If there are, more than one saleperson with maximum number of sales amount find all the salespersons. Return salesperson id.

Input:

Table: salemast

Structure:

FieldTypeNullKeyDefaultExtra
salesperson_idint(11)YES
order_id int(11)YES

Data:

salesperson_idorder_id
50011001
50021002
50031002
50041002
50051003
50061004

Sample Solution:

SQL Code(MySQL):

DROP TABLE  IF EXISTS salemast;
CREATE TABLE salemast(salesperson_id int,  order_id int);
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5001', '1001');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5002', '1002');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5003', '1002');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5004', '1002');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5005', '1003');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5006', '1004');
SELECT
    order_id
FROM
    salemast
GROUP BY order_id
ORDER BY COUNT(*) DESC
LIMIT 1;

Sample Output:

order_id|
--------|
    1002|

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Find Student Supporter.
Next: Big Cities.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8