Count Number of Male and Female from the city table using SQL

Показать описание

#sql #antosh
Count Number of Male and Female from the city table using SQL

Input
---------------
City Gender
Pune M
Pune M
Pune F
Pune M
Mumbai F
Mumbai F
Indore F
Indore M
Indore F

Output
------------

City Male_count Female_count Total_count
Mumbai 0 2 2
Pune 3 1 4
Indore 1 2 3

SQL Query
----------------

SELECT CITY,
COUNT(CASE WHEN GENDER='M' THEN 1 END) AS Male_count,
COUNT(CASE WHEN GENDER='F' THEN 1 END) AS Female_count,
COUNT(GENDER) AS Total_count
FROM SCG1
GROUP BY CITY;

-----------------------------------------
SQL Metadata
------------------
CREATE TABLE SCG1(CITY VARCHAR2(100), GENDER VARCHAR2(1));

INSERT INTO SCG1 VALUES('Pune','M');
INSERT INTO SCG1 VALUES('Pune','M');
INSERT INTO SCG1 VALUES('Pune','F');
INSERT INTO SCG1 VALUES('Pune','M');
INSERT INTO SCG1 VALUES('Mumbai','F');
INSERT INTO SCG1 VALUES('Mumbai','F');
INSERT INTO SCG1 VALUES('Indore','F');
INSERT INTO SCG1 VALUES('Indore','M');
INSERT INTO SCG1 VALUES('Indore','F');

COMMIT;

SELECT * FROM SCG1;

SELECT CITY,
COUNT(CASE WHEN GENDER='M' THEN 1 END) AS Male_count,
COUNT(CASE WHEN GENDER='F' THEN 1 END) AS Female_count,
COUNT(GENDER) AS Total_count
FROM SCG1
GROUP BY CITY;