﻿ SQL Challenges-1: Highest difference of sale of all quarters on a product - w3resource

# 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:

FieldTypeNullKeyDefaultExtra
company_idintNOPRI
qtr1_saleintYES
qtr2_saleintYES
qtr3_saleintYES
qtr4_saleintYES

Data:

company_idqtr1_saleqtr2_saleqtr3_saleqtr4_sale
1001240310330400
1002310250320280
1003370420400450
1004400340320350
1005270350340360
1006160200220200
1007340350370400
1008250280300350
1009350300280350
1010230260280300

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: