Crazy GROUP / UNION ALL

[unemployment]

Table Name: partners

 

partner_id user_id friend_id approved

      8               3       2       1

    11               1       3       1

 

I need to write a mysql query that sum's the total of partners each userid has. 

 

In this example, user_id 3 would have 2 friends, user_id 1 would have 1 friend and user_id 2(located as a friend_id) would have 1 friend.

[fenway]

Why not simply:

 

SELECT SUM( CASE WHEN user_id = 1 THEN 1 ELSE WHEN friend_id = 1 ELSE 0 END ) as friendCount FROM partners WHERE user_uid = 1 or friend_id = 1

[DavidAM]

If you are looking for the count for an individual user (say ID 3), I would use something like this:

 

SELECT COUNT(*) AS Cnt FROM partners WHERE 3 IN (user_id, friend_id);

This is going to do a table scan.

 

If you are looking for the count for ALL users, I would try something like this:

 

SELECT user_id, SUM(Cnt) AS Total FROM (
SELECT user_id, count(*) AS Cnt FROM partners GROUP BY user_id
UNION ALL
SELECT friend_id, count(*) FROM partners GROUP BY friend_id
) AS U
GROUP BY user_id;

This may do two table scans; or, if user_id and friend_id are both individual indexes, this may just scan the indexes.

 

If you are looking for an individual user and don't want to do a table scan; if user_id and friend_id are indexes, use the second query but with a WHERE clause inside and no need for the GROUP BY:

SELECT SUM(Cnt) AS Total FROM (
SELECT count(*) AS Cnt FROM partners WHERE user_id = 3
UNION ALL
SELECT count(*) FROM partners WHERE friend_id = 3
) AS U;

 

Of course, this requires version 5.something of mySql to use the SELECT as a psuedo table.