I need to group users column according to given year ranges. This is example year ranges (1980-1989,1990-1999,2000-2009) And then, I need to join to count results from another table. I created a database "user_relations" and I have created two tables to show my problem.
+--------------------------+
| Tables_in_user_relations |
+--------------------------+
| user_answers |
| users |
+--------------------------+
2 rows in set (0.00 sec)
And this is users table
+----+----------+-----------+
| id | username | birthyear |
+----+----------+-----------+
| 1 | user1 | 1980 |
| 2 | user2 | 1990 |
| 3 | user3 | 2000 |
| 4 | user4 | 1983 |
+----+----------+-----------+
4 rows in set (0.00 sec)
and this is user_answers table. I just need to count user_answers according to users which is grouped by date range.
+----+---------+-----------+
| id | user_id | option_id |
+----+---------+-----------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 3 | 2 |
| 4 | 4 | 1 |
+----+---------+-----------+
4 rows in set (0.00 sec)
According to these tables I need to get a result something like this.
+------------------------------------------------
| option_id | 1980-1989 | 1990-1999 | 2000-2009 |
+------------------------------------------------
| 1 | 2 | 1 | 0 |
| 2 | 0 | 0 | 1 |
| 3 | 0 | 0 | 0 |
+------------------------------------------------
Note: I need to use users table firstly. I know there may be many variations to get this results. But I have to get the result in this way. This is just an example.
I hope there is someone over there who helps me. Thank you.
You can use a case statement inside a sum for this, fairly simple:
select ua.option_id,
sum(case when u.birthyear between 1980 and 1989 then 1 else 0 end) as "1980-1989",
sum(case when u.birthyear between 1990 and 1999 then 1 else 0 end) as "1990-1999",
sum(case when u.birthyear between 2000 and 2009 then 1 else 0 end) as "2000-2009"
from users as u
inner join user_answers as ua
on ua.user_id = u.id
group by ua.option_id