SQL Challenges-1: Number of customers, orders, and total order amount for each city

SQL Challenges-1: Exercise-71 with Solution

From the following tables write a query in SQL to find the number of customer issued orders, number of orders they have issued along with the total order amount for each cities. Those cities should come in result set where four or more orders have been issued. Returns city name, number of customers, number of orders and total order amount.

Table: customer

Structure:

Data:

Table: orders

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),
cust_country	varchar(25),
grade			integer
);



insert into customer values('C00013','Holmes     ','London    ','UK       ', 2);
insert into customer values('C00001','Micheal    ','New York  ','USA      ', 2);
insert into customer values('C00020','Albert     ','New York  ','USA      ', 3);
insert into customer values('C00025','Ravindran  ','Bangalore ','India    ', 2);
insert into customer values('C00024','Cook       ','London    ','UK       ', 2);
insert into customer values('C00015','Stuart     ','London    ','UK       ', 1);
insert into customer values('C00002','Bolt       ','New York  ','USA      ', 3);
insert into customer values('C00018','Fleming    ','Brisban   ','Australia', 2);
insert into customer values('C00021','Jacks      ','Brisban   ','Australia', 1);
insert into customer values('C00019','Yearannaidu','Chennai   ','India    ', 1);
insert into customer values('C00005','Sasikant   ','Mumbai    ','India    ', 1);
insert into customer values('C00007','Ramanathan ','Chennai   ','India    ', 1);
insert into customer values('C00022','Avinash    ','Mumbai    ','India    ', 2);
insert into customer values('C00004','Winston    ','Brisban   ','Australia', 1);
insert into customer values('C00023','Karl       ','London    ','UK       ', 0);
insert into customer values('C00006','Shilton    ','Torento   ','Canada   ', 1);
insert into customer values('C00010','Charles    ','Hampshair ','UK       ', 3);
insert into customer values('C00017','Srinivas   ','Bangalore ','India    ', 2);
insert into customer values('C00012','Steven     ','San Jose  ','USA      ', 1);
insert into customer values('C00008','Karolina   ','Torento   ','Canada   ', 1);
insert into customer values('C00003','Martin     ','Torento   ','Canada   ', 2);
insert into customer values('C00009','Ramesh     ','Mumbai    ','India    ', 3);
insert into customer values('C00014','Rangarappa ','Bangalore ','India    ', 2);
insert into customer values('C00016','Venkatpati ','Bangalore ','India    ', 2);
insert into customer values('C00011','Sundariya  ','Chennai   ','India    ', 3);

	

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



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


SELECT cust_city, COUNT(o.ord_num) as "Number of orders", 
COUNT(distinct c.cust_code) as "Number of Customer", 
SUM(ord_amount) as "Total order Amountt"
FROM customer c LEFT JOIN orders o
ON c.cust_code = o.cust_code
GROUP BY cust_city
HAVING COUNT(o.ord_num) >= 4

Sample Output:

cust_city |Number of orders|Number of Customer|Total order Amountt|
----------+----------------+------------------+-------------------+
Bangalore |               5|                 4|               8300|
Chennai   |               5|                 3|              17000|
London    |               4|                 4|               6500|
Mumbai    |               7|                 3|              12700|
Torento   |               4|                 3|               9500|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Resolve highest complaint and nearest manager id is 114.
Next: Find highest three unique salaries of each department.