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
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;