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

Last update on May 30 2022 07:33:25 (UTC/GMT +8 hours)

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:

Field	Type	Null	Key	Default	Extra
salesperson_id	int(11)	YES
order_id	int(11)	YES

Data:

salesperson_id	order_id
5001	1001
5002	1002
5003	1002
5004	1002
5005	1003
5006	1004

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

SQL Server SELECT into existing table.

INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key

Database: SQL Server

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