How to count certain the ages of people who have a log record from another table in sql?-CodePudding

I want to get a count of how many people who are 18 are recorded in the logs table only once. Now if I have the same person who entered 2 times, I can see that there are 2 people with age 18. I can't make it appear only once. How do I do this???

My logs table and people table are connected by card_id. My logs table has the login date and card_id.

While my members' table has the birthdate and card_id columns.

HERE is the query I made

select 
 card_id, sum("18") as "18" 
from 
 ( select logs.login, members.card_id, 
    count(distinct (case when 0 <= age and age <= 18 then age end)) as "18", 
    count(  (case when 19 <= age and age <= 30 then age end)) as "30",
    count ( (case when 31 <= age and age <= 50 then age end)) as "50" 
   from 
   (select login, date_part('year', age(birthdate)) as age, members.card_id as card_id, 
     logs.login 
     from members 
     left join logs on logs.card_id=members.card_id
    ) as members 
   left join logs on logs.card_id=members.card_id
   group by logs.login, members.card_id 
  ) as members
  where login <= '20221029' group by card_id;

I want to create a table like this:

18 | 30 | 50 |
---------------
2  | 0  | 0

CodePudding user response：

Count the distinct card_id-s.

select count(distinct card_id)
from members join logs using (card_id)
where extract('year' from age(birthdate)) = 18
  and login <= '20221029';

Unrelated but it seems that you are storing login as text. This is not a good idea. Use type date instead.

Addition afer the question update

select  count(*) filter (where user_age = 18) as age_18,
        count(*) filter (where user_age between 19 and 30) as age_30,
        count(*) filter (where user_age between 31 and 50) as age_50
from
(
 select distinct on (card_id) 
    extract('year' from age(birthdate)) user_age
 from members inner join logs using (card_id)
 where login <= '20221029'
 order by card_id, login desc -- pick the latest login
) AS t;