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.
New contributor
Cameron Smith is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
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.
1