SQL Challenges-1: Highest difference in total sale of all quarters on a product of many companies

SQL Challenges-1: Exercise-53 with Solution

From the following table write a SQL query to identifies the highest difference in total sale of all quarters on a product of many companies. Return highest and lowest total sale and highest difference.

Input:

Table: sales

Structure:

Data:

Sample Solution:

SQL Code(MySQL):

create table sales (
company_id int(4) not null unique,
qtr1_sale	int(4),
qtr2_sale 	int(4),
qtr3_sale 	int(4),
qtr4_sale 	INT(4));



insert into sales values(1001,	240,	310,	330,	400);
insert into sales values(1002,	310,	250,	320,	280);
insert into sales values(1003,	370,	420,	400,	450);
insert into sales values(1004,	400,	340,	320,	350);
insert into sales values(1005,	270,	350,	340,	360);
insert into sales values(1006,	160,	200,	220,	200);
insert into sales values(1007,	340,	350,	370,	400);
insert into sales values(1008,	250,	280,	300,	350);
insert into sales values(1009,	350,	300,	280,	350);
insert into sales values(1010,	230,	260,	280,	300);


SELECT max(total_sale) AS max_sale,
MIN(total_sale) AS min_sale, 
MAX(total_sale)-min(total_sale) AS sale_difference
FROM
  (SELECT company_id,
          sum(qtr1_sale+qtr2_sale+qtr3_sale+qtr4_sale) AS total_sale
   FROM sales
   GROUP BY company_id) a;

Sample Output:

max_sale|min_sale|sale_difference|
--------+--------+---------------+
    1640|     780|            860|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Compare the purchasing status of the average purchase quantity of products of a category to the average pruchase quantity of the distributor.
Next: Find smart salesperson executed at least 5 transactions.