I have a database like facebook. A query asks me to count how many Male friends, Female friends and Null friends (no sex specificated).
I have this query so far, but it only returns 1 row, not all the users.
create or replace view friends as
sum(if(fs.sex = 'M', 1, 0)) as Male_Friends,
sum(if(fs.sex = 'F', 1, 0)) as Female_Friends,
sum(if(fs.sex = ' ', 1, 0)) as Friends_Undefined_Sex
from friends_sex fs;
Your query is malformed. The problem is that you have columns in the
select (such as 'fs.user_id
) that are not in thegroup by`. This is allowed in MySQL, but not in other databases. It tends to just cause confusion.
Because of the aggregation functions, the query is an aggregation query. Without a
group by it returns one row. So you need a
select fs.user_id, sum(fs.sex = 'M') as Male_Friends, sum(fs.sex = 'F') as Female_Friends, sum(fs.sex = ' ') as Friends_Undefined_Sex from friends_sex fs group by fs.user_id;
Note that in MySQL you don't need
if(). MySQL treats booleans as integers in integer contexts, with "1" for true and "0" for false.
Also note that there is no
fs.user_id_friend to return; presumably, many people have more than one friend. If you want a list, then include:
to get a comma-delimited list of the friends' ids.