Monday, 28 October 2019

Find the total number of customers from each country in the table (customer ID, customer name, country) using group by.

create table customer
     (cust_ID integer primary key,
     cust_name char(20),
     country char(10)
     );

mysql> desc customer;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| cust_ID   | int(11)  | NO   | PRI | NULL    |       |
| cust_name | char(20) | YES  |     | NULL    |       |
| country   | char(10) | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

insert into customer values(101,"Bhuvanesh","Pakistan");
insert into customer values(102,"Aniket","India");
insert into customer values(103,"Devesh","Srilanka");
insert into customer values(104,"Jashan","Pakistan");
insert into customer values(105,"Jatin","Japan");
insert into customer values(106,"Apar","India");
insert into customer values(107,"Ashish","Nepal");
insert into customer values(108,"Priyanshu","Srilanka");
insert into customer values(109,"Vasu","Bangladesh");
insert into customer values(110,"Omprakash","Japan");
insert into customer values(111,"Prateek","India");

mysql> select * from customer;
+---------+-----------+------------+
| cust_ID | cust_name | country    |
+---------+-----------+------------+
|     101 | Bhuvanesh | Pakistan   |
|     102 | Aniket    | India      |
|     103 | Devesh    | Srilanka   |
|     104 | Jashan    | Pakistan   |
|     105 | Jatin     | Japan      |
|     106 | Apar      | India      |
|     107 | Ashish    | Nepal      |
|     108 | Priyanshu | Srilanka   |
|     109 | Vasu      | Bangladesh |
|     110 | Omprakash | Japan      |
|     111 | Prateek   | India      |
+---------+-----------+------------+
11 rows in set (0.10 sec)

mysql> select distinct(country) from customer;
+------------+
| country    |
+------------+
| Pakistan   |
| India      |
| Srilanka   |
| Japan      |
| Nepal      |
| Bangladesh |
+------------+
6 rows in set (0.03 sec)


mysql> select country, count(country) from customer group by country;
+------------+----------------+
| country    | count(country) |
+------------+----------------+
| Pakistan   |              2 |
| India      |                3 |
| Srilanka   |              2 |
| Japan      |              2 |
| Nepal      |              1 |
| Bangladesh |              1 |
+------------+----------------+
6 rows in set (0.04 sec)






No comments:

Post a Comment