[SOLVED] Two select statements in one Query

Two select statements in one Query

I'm new to SQL and I'm having trouble with a pretty basic problem. I have a with column: "primaryProfession" in this column there are actors and actresses. I want to return a result that shows how many Actors there are total and how many Actresses with one query.

This is my code:

SELECT (SELECT COUNT(primaryProfession), 
        FROM title      
        WHERE primaryProfession = "Actor" ) AS "Actors",
        (SELECT COUNT(primaryProfession), 
        FROM title
        WHERE primaryProfession = "Actress") AS "Actresses" 
FROM title;

I want it to produce two columns: "Actors" and "Actresses" and show me the total number of each.

Solution

You may use conditional aggregation here:

SELECT
    SUM(primaryProfession = 'Actor') AS Actors,
    SUM(primaryProfession = 'Actress') AS Actresses
FROM title;

This approach is succinct and also only requires SQLite to make a single pass over the title table.