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

 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;