﻿ SQL Challenges-1: Find cost percent for each order of total order amount - w3resource

# SQL Challenges-1: Find cost percent for each order of total order amount

## SQL Challenges-1: Exercise-69 with Solution

From the following tables write a query in SQL to find the cost percent for each order of total order amount of a customer. Return customer ID, customer name, order details, order amount and cost percent.

Table: orders

Structure:

FieldTypeNullKeyDefaultExtra
ord_numintNOPRI
ord_amountintYES
advance_amountintYES
ord_datedateYES
cust_codevarchar(10)YESMUL
ord_descriptionvarchar(25)YES

Data:

ord_numord_amountadvance_amountord_datecust_codeord_description
20010010006002008-01-08C00015 RAM
200102 2000 300 2008-05-25 C00012 mother board
200105 2500 500 2008-07-18 C00025 RAM
200107 4500 900 2008-08-30 C00007 hard disk
200108 4000 600 2008-02-15 C00008 mouse
200109 3500 800 2008-07-30 C00015 key board
200113 4000 600 2008-06-10 C00022 key board
200116 500 100 2008-07-13 C00010 mouse
200118 500 100 2008-07-20 C00022 mouse
200119 4000 700 2008-09-16 C00007 RAM
200121 1500 600 2008-09-23 C00008 key board
200122 2500 400 2008-09-16 C00007 mother board
200123 500 100 2008-09-16 C00022 mother board
200125 2000 600 2008-10-10 C00008 hard disk
200126 500 100 2008-06-24 C00022 hard disk
200127 2500 400 2008-07-20 C00015 mouse
200128 3500 1500 2008-07-20 C00009 mother board
200129 2500 500 2008-07-20 C00022 RAM
200130 2500 400 2008-07-30 C00025 mother board
200131 900 150 2008-08-26 C00012 hard disk
200133 1200 400 2008-06-29 C00009 mouse

Table: customer

Structure:

FieldTypeNullKeyDefaultExtra
cust_codevarchar(10)NOPRI
cust_namevarchar(25)YES
cust_cityvarchar(25)YES
working_areavarchar(25)YES
cust_countryvarchar(25)YES
grade intYES
opening_amtdecimal(10,2)YES
receive_amtdecimal(10,2)YES
payment_amtdecimal(10,2)YES
outstanding_amt decimal(10,2)YES
phone_no varchar(15)YES

Data:

cust_codecust_namecust_cityworking_areacust_countrygradeopening_amtreceive_amtpayment_amtoutstanding_amtphone_no
C00001MichealNew YorkNew YorkUSA23000.005000.002000.00 6000.00 CCCCCCC
C00002BoltNew YorkNew YorkUSA35000.007000.009000.00 3000.00 DDNRDRH
C00003MartinTorentoTorentoCanada28000.007000.007000.00 8000.00 MJYURFD
C00004WinstonBrisbanBrisbanAustralia15000.008000.007000.00 6000.00 AAAAAAA
C00005SasikantMumbaiMumbaiIndia17000.0011000.007000.00 11000.00 147-25896312
C00006ShiltonTorentoTorentoCanada110000.007000.006000.00 11000.00 DDDDDDD
C00007RamanathanChennaiChennaiIndia17000.0011000.009000.00 9000.00 GHRDWSD
C00008KarolinaTorentoTorentoCanada17000.007000.009000.00 5000.00 HJKORED
C00009RameshMumbaiMumbaiIndia38000.007000.003000.00 12000.00 Phone No
C00010CharlesHampshairHampshairUK36000.004000.005000.00 5000.00 MMMMMMM
C00011SundariyaChennaiChennaiIndia37000.0011000.007000.00 11000.00 PPHGRTS
C00012StevenSan JoseSan JoseUSA15000.007000.009000.00 3000.00 KRFYGJK
C00013HolmesLondonLondonUK26000.005000.007000.00 4000.00 BBBBBBB
C00014RangarappaBangaloreBangaloreIndia28000.0011000.007000.00 12000.00 AAAATGF
C00015StuartLondonLondonUK16000.008000.003000.00 11000.00 GFSGERS
C00016VenkatpatiBangaloreBangaloreIndia28000.0011000.007000.00 12000.00 JRTVFDD
C00017SrinivasBangaloreBangaloreIndia28000.004000.003000.00 9000.00 AAAAAAB
C00018FlemingBrisbanBrisbanAustralia27000.007000.009000.00 5000.00 NHBGVFC
C00019YearannaiduChennaiChennaiIndia18000.007000.007000.00 8000.00 ZZZZBFV
C00020AlbertNew YorkNew YorkUSA35000.007000.006000.00 6000.00 BBBBSBB
C00021JacksBrisbanBrisbanAustralia17000.007000.007000.00 7000.00 WERTGDF
C00022AvinashMumbaiMumbaiIndia27000.0011000.009000.00 9000.00 113-12345678
C00023KarlLondonLondonUK04000.006000.007000.00 3000.00 AAAABAA
C00024CookLondonLondonUK24000.009000.007000.00 6000.00 FSDDSDF
C00025RavindranBangaloreBangaloreIndia25000.007000.004000.00 8000.00 AVAVAVA

Sample Solution:

SQL Code(MySQL):

``````create table customer(
cust_code		varchar(10) not null unique,
cust_name		varchar(25),
cust_city		varchar(25),
working_area	varchar(25),
cust_country	varchar(25),
grade			integer,
opening_amt		decimal(10,2),
receive_amt		decimal(10,2),
payment_amt		decimal(10,2),
outstanding_amt  decimal(10,2),
phone_no 		varchar(15)
);

insert into customer values('C00013','Holmes     ','London    ','London   ','UK       ', 2, 6000.00, 5000.00,7000.00, 4000.00,'BBBBBBB     ');
insert into customer values('C00001','Micheal    ','New York  ','New York ','USA      ', 2, 3000.00, 5000.00,2000.00, 6000.00,'CCCCCCC     ');
insert into customer values('C00020','Albert     ','New York  ','New York ','USA      ', 3, 5000.00, 7000.00,6000.00, 6000.00,'BBBBSBB     ');
insert into customer values('C00025','Ravindran  ','Bangalore ','Bangalore','India    ', 2, 5000.00, 7000.00,4000.00, 8000.00,'AVAVAVA     ');
insert into customer values('C00024','Cook       ','London    ','London   ','UK       ', 2, 4000.00, 9000.00,7000.00, 6000.00,'FSDDSDF     ');
insert into customer values('C00015','Stuart     ','London    ','London   ','UK       ', 1, 6000.00, 8000.00,3000.00,11000.00,'GFSGERS     ');
insert into customer values('C00002','Bolt       ','New York  ','New York ','USA      ', 3, 5000.00, 7000.00,9000.00, 3000.00,'DDNRDRH     ');
insert into customer values('C00018','Fleming    ','Brisban   ','Brisban  ','Australia', 2, 7000.00, 7000.00,9000.00, 5000.00,'NHBGVFC     ');
insert into customer values('C00021','Jacks      ','Brisban   ','Brisban  ','Australia', 1, 7000.00, 7000.00,7000.00, 7000.00,'WERTGDF     ');
insert into customer values('C00019','Yearannaidu','Chennai   ','Chennai  ','India    ', 1, 8000.00, 7000.00,7000.00, 8000.00,'ZZZZBFV     ');
insert into customer values('C00005','Sasikant   ','Mumbai    ','Mumbai   ','India    ', 1, 7000.00,11000.00,7000.00,11000.00,'147-25896312');
insert into customer values('C00007','Ramanathan ','Chennai   ','Chennai  ','India    ', 1, 7000.00,11000.00,9000.00, 9000.00,'GHRDWSD     ');
insert into customer values('C00022','Avinash    ','Mumbai    ','Mumbai   ','India    ', 2, 7000.00,11000.00,9000.00, 9000.00,'113-12345678');
insert into customer values('C00004','Winston    ','Brisban   ','Brisban  ','Australia', 1, 5000.00, 8000.00,7000.00, 6000.00,'AAAAAAA     ');
insert into customer values('C00023','Karl       ','London    ','London   ','UK       ', 0, 4000.00, 6000.00,7000.00, 3000.00,'AAAABAA     ');
insert into customer values('C00006','Shilton    ','Torento   ','Torento  ','Canada   ', 1,10000.00, 7000.00,6000.00,11000.00,'DDDDDDD     ');
insert into customer values('C00010','Charles    ','Hampshair ','Hampshair','UK       ', 3, 6000.00, 4000.00,5000.00, 5000.00,'MMMMMMM     ');
insert into customer values('C00017','Srinivas   ','Bangalore ','Bangalore','India    ', 2, 8000.00, 4000.00,3000.00, 9000.00,'AAAAAAB     ');
insert into customer values('C00012','Steven     ','San Jose  ','San Jose ','USA      ', 1, 5000.00, 7000.00,9000.00, 3000.00,'KRFYGJK     ');
insert into customer values('C00008','Karolina   ','Torento   ','Torento  ','Canada   ', 1, 7000.00, 7000.00,9000.00, 5000.00,'HJKORED     ');
insert into customer values('C00003','Martin     ','Torento   ','Torento  ','Canada   ', 2, 8000.00, 7000.00,7000.00, 8000.00,'MJYURFD     ');
insert into customer values('C00009','Ramesh     ','Mumbai    ','Mumbai   ','India    ', 3, 8000.00, 7000.00,3000.00,12000.00,'Phone No    ');
insert into customer values('C00014','Rangarappa ','Bangalore ','Bangalore','India    ', 2, 8000.00,11000.00,7000.00,12000.00,'AAAATGF     ');
insert into customer values('C00016','Venkatpati ','Bangalore ','Bangalore','India    ', 2, 8000.00,11000.00,7000.00,12000.00,'JRTVFDD     ');
insert into customer values('C00011','Sundariya  ','Chennai   ','Chennai  ','India    ', 3, 7000.00,11000.00,7000.00,11000.00,'PPHGRTS     ');

create table orders(
ord_num		integer(10) not null unique,
ord_amount		integer,
advance_amount		integer,
ord_date	date,
cust_code	varchar(10),
ord_description		varchar(25),
foreign key(cust_code) references customer(cust_code)
);

insert into orders values(200122, 2500, 400,'2008-09-16','C00007','mother board');
insert into orders values(200118,  500, 100,'2008-07-20','C00022','mouse');
insert into orders values(200119, 4000, 700,'2008-09-16','C00007','RAM');
insert into orders values(200121, 1500, 600,'2008-09-23','C00008','key board');
insert into orders values(200130, 2500, 400,'2008-07-30','C00025','mother board');
insert into orders values(200108, 4000, 600,'2008-02-15','C00008','mouse');
insert into orders values(200105, 2500, 500,'2008-07-18','C00025','RAM');
insert into orders values(200109, 3500, 800,'2008-07-30','C00015','key board');
insert into orders values(200125, 2000, 600,'2008-10-10','C00008','hard disk');
insert into orders values(200123,  500, 100,'2008-09-16','C00022','mother board');
insert into orders values(200116,  500, 100,'2008-07-13','C00010','mouse');
insert into orders values(200126,  500, 100,'2008-06-24','C00022','hard disk');
insert into orders values(200129, 2500, 500,'2008-07-20','C00022','RAM');
insert into orders values(200127, 2500, 400,'2008-07-20','C00015','mouse');
insert into orders values(200128, 3500,1500,'2008-07-20','C00009','mother board');
insert into orders values(200131,  900, 150,'2008-08-26','C00012','hard disk');
insert into orders values(200133, 1200, 400,'2008-06-29','C00009','mouse');
insert into orders values(200100, 1000, 600,'2008-01-08','C00015','RAM');
insert into orders values(200107, 4500, 900,'2008-08-30','C00007','hard disk');
insert into orders values(200113, 4000, 600,'2008-06-10','C00022','key board');
insert into orders values(200102, 2000, 300,'2008-05-25','C00012','mother board');

select
c.cust_code,
c.cust_name,
o.ord_description,
o.ord_amount,
o.ord_amount / sum(o.ord_amount)
OVER (PARTITION BY c.cust_name) AS "Percentage of Cost"
FROM
orders o
JOIN
customer c
ON o.cust_code = c.cust_code
```
```

Sample Output:

```cust_code|cust_name  |ord_description|ord_amount|Percentage of Cost|
---------+-----------+---------------+----------+------------------+
C00022   |Avinash    |key board      |      4000|            0.5000|
C00022   |Avinash    |mouse          |       500|            0.0625|
C00022   |Avinash    |mother board   |       500|            0.0625|
C00022   |Avinash    |hard disk      |       500|            0.0625|
C00022   |Avinash    |RAM            |      2500|            0.3125|
C00010   |Charles    |mouse          |       500|            1.0000|
C00008   |Karolina   |mouse          |      4000|            0.5333|
C00008   |Karolina   |key board      |      1500|            0.2000|
C00008   |Karolina   |hard disk      |      2000|            0.2667|
C00007   |Ramanathan |hard disk      |      4500|            0.4091|
C00007   |Ramanathan |RAM            |      4000|            0.3636|
C00007   |Ramanathan |mother board   |      2500|            0.2273|
C00009   |Ramesh     |mother board   |      3500|            0.7447|
C00009   |Ramesh     |mouse          |      1200|            0.2553|
C00025   |Ravindran  |RAM            |      2500|            0.5000|
C00025   |Ravindran  |mother board   |      2500|            0.5000|
C00012   |Steven     |mother board   |      2000|            0.6897|
C00012   |Steven     |hard disk      |       900|            0.3103|
C00015   |Stuart     |RAM            |      1000|            0.1429|
C00015   |Stuart     |key board      |      3500|            0.5000|
C00015   |Stuart     |mouse          |      2500|            0.3571|
```

SQL Code Editor:

Contribute your code and comments through Disqus.

﻿

Follow us on Facebook and Twitter for latest update.