SQL Challenges-1: Find total order amount for each customer in September 2008

SQL Challenges-1: Exercise-65 with Solution

From the following table write a SQL query to calculate the total order amount issued by each customer in September 2008. Return customer ID and total order amount.

Table: orders

Structure:

Data:

Table: customer

Structure:

Data:

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(200114, 3500,2000,'2008-08-15','C00002',' ');
 insert into orders values(200122, 2500, 400,'2008-09-16','C00003',' ');
 insert into orders values(200118,  500, 100,'2008-07-20','C00023',' ');
 insert into orders values(200119, 4000, 700,'2008-09-16','C00007',' ');
 insert into orders values(200121, 1500, 600,'2008-09-23','C00008',' ');
 insert into orders values(200130, 2500, 400,'2008-07-30','C00025',' ');
 insert into orders values(200134, 4200,1800,'2008-09-25','C00004',' ');
 insert into orders values(200108, 4000, 600,'2008-02-15','C00008',' ');
 insert into orders values(200103, 1500, 700,'2008-05-15','C00021',' ');
 insert into orders values(200105, 2500, 500,'2008-07-18','C00025',' ');
 insert into orders values(200109, 3500, 800,'2008-07-30','C00011',' ');
 insert into orders values(200101, 3000,1000,'2008-07-15','C00001',' ');
 insert into orders values(200111, 1000, 300,'2008-07-10','C00020',' ');
 insert into orders values(200104, 1500, 500,'2008-03-13','C00006',' ');
 insert into orders values(200106, 2500, 700,'2008-04-20','C00005',' ');
 insert into orders values(200125, 2000, 600,'2008-10-10','C00018',' ');
 insert into orders values(200117,  800, 200,'2008-10-20','C00014',' ');
 insert into orders values(200123,  500, 100,'2008-09-16','C00022',' ');
 insert into orders values(200120,  500, 100,'2008-07-20','C00009',' ');
 insert into orders values(200116,  500, 100,'2008-07-13','C00010',' ');
 insert into orders values(200124,  500, 100,'2008-06-20','C00017',' ');
 insert into orders values(200126,  500, 100,'2008-06-24','C00022',' ');
 insert into orders values(200129, 2500, 500,'2008-07-20','C00024',' ');
 insert into orders values(200127, 2500, 400,'2008-07-20','C00015',' ');
 insert into orders values(200128, 3500,1500,'2008-07-20','C00009',' ');
 insert into orders values(200135, 2000, 800,'2008-09-16','C00007',' ');
 insert into orders values(200131,  900, 150,'2008-08-26','C00012',' ');
 insert into orders values(200133, 1200, 400,'2008-06-29','C00009',' ');
 insert into orders values(200100, 1000, 600,'2008-01-08','C00015',' ');
 insert into orders values(200110, 3000, 500,'2008-04-15','C00019',' ');
 insert into orders values(200107, 4500, 900,'2008-08-30','C00007',' ');
 insert into orders values(200112, 2000, 400,'2008-05-30','C00016',' ');
 insert into orders values(200113, 4000, 600,'2008-06-10','C00022',' ');
 insert into orders values(200102, 2000, 300,'2008-05-25','C00012',' ');


SELECT cust_code, SUM(ord_amount) as 'Total Order'
FROM orders 
WHERE ord_date BETWEEN '2008-09-01' AND '2008-09-30'
GROUP BY cust_code
ORDER BY SUM(ord_amount) DESC;

Sample Output:

cust_code|Total Order|
---------+-----------+
C00007   |       6000|
C00004   |       4200|
C00003   |       2500|
C00008   |       1500|
C00022   |        500|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Count the number of patients treated by each doctor.
Next: Display own salary and average salary for their department.