SQL Percentage of True columns

Question

I have a table where each row has a description field as well as a boolean value. I'm trying to write a query where I can group by each respective description, and see the percentage of times that the boolean was true.

Example table:

    PID    Gender    SeniorCitizen
     1       M             1
     2       M             1
     3       F             0
     4       F             1
     5       M             0

And I want a query that will return this:

    Gender    SeniorPct
      M         .66
      F         .50

I've got to the point where I have a query that will calculate the individual percentages for a male or female - but I want to see both results at once

    SELECT Gender, COUNT(*) * 1.0 / 
                   (SELECT COUNT(*) FROM MyTable WHERE Gender='M') 
    FROM MyTable WHERE Gender='M' and SeniorCitizen=1;

I've been trying to include a "GROUP BY Gender" statement in my outer SELECT above, but I can't seem to figure out how to tweak the inner SELECT to get the correct results after tweaking the outer SELECT as such.

Answer 1

(I tested this under MySQL, please check if the same idea can be applied to the SQLite.)

To find the number of seniors (per gender), we can treat the bits as numbers and simply sum them up:

SELECT
    Gender,
    SUM(SeniorCitizen) Seniors
FROM MyTable
GROUP BY Gender

GENDER  SENIORS
M       2
F       1

Based on that, we can easily calculate percentages:

SELECT
    Gender,
    SUM(SeniorCitizen) / COUNT(*) * 100 SeniorsPct
FROM MyTable
GROUP BY Gender

GENDER  SENIORSPCT
M       66.6667
F       50

You can play with it in this SQL Fiddle.

---

UPDATE: Very similar idea works under SQLite as well. Please take a look at another SQL Fiddle.

Answer 2

Try the following:

CREATE TABLE #MyTable
(
    PID INT,
    Gender VARCHAR(1),
    SeniorCitizen BIT
)

INSERT INTO #MyTable
(
    PID,
    Gender,
    SeniorCitizen
)
SELECT 1, 'M', 1 UNION
SELECT 2, 'M', 1 UNION
SELECT 3, 'F', 0 UNION
SELECT 4, 'F', 1 UNION
SELECT 5, 'M', 0

SELECT
    Gender,
    COUNT(CASE WHEN SeniorCitizen = 1 THEN 1 END), -- Count of SeniorCitizens grouped by Gender
    COUNT(1), -- Count of all users grouped by Gender
    CONVERT(DECIMAL(2, 2), -- You can ignore this if you want
        COUNT(CASE WHEN SeniorCitizen = 1 THEN 1 END) * 1.0 / COUNT(1) -- Your ratio
        )
FROM
    #MyTable
GROUP BY
    Gender