How to combine 2 SQL queries of same table into as separate columns-CodePudding

If I want to show the total number of male patients and the total number of female patients in the patients table having gender as a column as: male_count female_count 1220 1105

How do I do this ? structure of patients table (patient_id INT, first_name CHAR, last_name CHAR, city CHAR, birth_date DATE and gender CHAR)

Entries in gender column include 'M' and 'F'

I am trying to learn SQL. Please revert with a possible solution

CodePudding user response：

You can use subqueries like this

SELECT
 (SELECT count(*) FROM patients WHERE gender = 'F') as female_count,
 (SELECT count(*) FROM patients WHERE gender = 'M') as male_count
;

Not the prettiest way to do it, but should work

CodePudding user response：

There's a few ways to do this, one example would be a pivot:

select *
from
(
    select patient_id
        , gender
    from patients
) a
pivot (
    count(patient_id) for gender in ([M], [F])
) p

An easy way to alias these to the column names you want is to make it a subquery:

select q.m male_count
    , q.f female_count
from (
    select *
    from
    (
        select patient_id
            , gender
        from patients
    ) a
    pivot (
        count(patient_id) for gender in ([M], [F])
    ) p
) q

You didn't specify a DBMS, so I've gone with SQL Server / TSQL for this example.

CodePudding user response：

You can use CASE statements and SUM the results for each column.

SELECT
    SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,
    SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count
FROM patients;