Nil Marti Hermans Nil Marti Hermans - 5 months ago 34
MySQL Question

SUMIF Query Returning One Line

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

select fs.user_id,
fs.user_id_friend,
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;


This prints me only one row ans I need it to print all the users, can anyone please help me out?

If you need more info about the tables or views ask for it, I'm quite new to this forum so excuse me for any mistakes.

Answer

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 group by:

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:

group_concat(fs.user_id_friend)

to get a comma-delimited list of the friends' ids.