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.

  • 1
    Show us the CREATE TABLE Visitors ... statement and a sample of few rows. Are you sure you have both user and users as columns in this table? – ypercubeᵀᴹ Oct 14 '18 at 11:43
  • Sorry there was a typing error...resolved. I imported the table from a cvs file. – Javeria Habib Oct 14 '18 at 12:39
  • can a single user show up more than once in the Visitors table? if the answer is 'yes', what do you expect to happen if the user has 2 different values for birthyear ... one in the desired range and one outside of the desired range? – markp-fuso Oct 14 '18 at 22:59
  • what datatype is Visitors.birthyear? have you verified there are users with birthyear 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
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.
  • Whenever a subquery is used, it must have parentheses around it.
  • The 'composite' index INDEX(userid, birthyear) would help performance some.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.