Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have a list of users with corresponding date of birth. Is there any possibility I can get a percentage of users born between 1990 and 2000 in a single SQL query?
The query I tried is as follows:
Select (Select count(distinct user) as users from Visitors
where birthyear between 1990 and 2000)/count(distinct user) from Visitors
But it gives me a zero as result.
SELECT 100.0 * SUM(birthyear between 1990 and 2000) /
COUNT(*) AS pct
FROM ( SELECT userid, MAX(birthyear) AS birthyear
FROM visitors GROUP BY userid ) AS x
Some notes:
SUM(boolean-expr) is a way to count how often it is TRUE.between 1990 and 2000 includes 101 distinct years because BETWEEN is 'inclusive'. Are you sure you wanted that?COUNT(*) will count all the rows from the derived table (the subquery)MAX is a kludge -- A visitor might put down different birthyears on different visits.INDEX(userid, birthyear) would help performance some.
CREATE TABLE Visitors ...statement and a sample of few rows. Are you sure you have bothuserandusersas columns in this table? – ypercubeᵀᴹ Oct 14 '18 at 11:43Visitorstable? if the answer is 'yes', what do you expect to happen if the user has 2 different values forbirthyear... one in the desired range and one outside of the desired range? – markp-fuso Oct 14 '18 at 22:59Visitors.birthyear? have you verified there are users withbirthyear between 1990 and 2000, ie, what does the following return:Select count(distinct user) from Visitors where birthyear between 1990 and 2000? – markp-fuso Oct 15 '18 at 15:28