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.
(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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With