CODEWITHSUNDEEP
phpsqlcount
Dan Cantir
Dan Cantir

Reputation: 177

two counts in one query

I have this table:

id  | id_user  |  id_user_stalkers | date
0   |  0222222 |  032332         | 32234234

so, i want to know count of all users that are in my friends list, and cout of all users that have me in their friend list. at the moment, i've made this query :

SELECT (
                    SELECT COUNT(id_user)
                    FROM   stalkers WHERE id_user =  ".$id."
                    ) AS user_stalkers,
                    (
                    SELECT COUNT(id_user_stalkers)
                    FROM   stalkers WHERE id_user_stalkers = ".$id."
                    ) AS user_is_stalked
            FROM stalkers

but it returns this:

Array
(
    [0] => Array
        (
            [user_stalkers] => 7
            [user_is_stalked] => 2
        )

    [1] => Array
        (
            [user_stalkers] => 7
            [user_is_stalked] => 2
        )

    [2] => Array
        (
            [user_stalkers] => 7
            [user_is_stalked] => 2
        )

    [3] => Array
        (
            [user_stalkers] => 7
            [user_is_stalked] => 2
        ))

it's all right, but i need only one row, not four.

Can somebody help me please ?

Upvotes: 1

Views: 101

Answers (5)

RThomas
RThomas

Reputation: 10882

Since all your fields are subqueries you can drop the from clause altogether. Otherwise you get a subquery for every single row in the table. This not only gets the results you want but reduces total hit on the database by a potentially very large amount (increasing performance).

SELECT 
(
 SELECT COUNT(id_user)
 FROM   stalkers WHERE id_user =  ".$id."
) AS user_stalkers,
(
 SELECT COUNT(id_user_stalkers)
 FROM   stalkers WHERE id_user_stalkers = ".$id."
) AS user_is_stalked

If this is going to be a potentially large table you'll want to make sure id_user and id_user_stalkers are both indexed. If an index can be used these sub-queries will be much much faster.

Upvotes: 1

rs.
rs.

Reputation: 27427

Try this (you dont have to query twice and worry about distinct etc)

SELECT COUNT(case when id_user =  ".$id." then 1 else null) AS user_stalkers,
COUNT(case when id_user_stalkers =  ".$id." then 1 else null) AS user_is_stalked
FROM   stalkers

Upvotes: 0

Louis Ricci
Louis Ricci

Reputation: 21086

         SELECT (
                SELECT COUNT(id_user)
                FROM   stalkers WHERE id_user =  ".$id."
                ) AS user_stalkers,
                (
                SELECT COUNT(id_user_stalkers)
                FROM   stalkers WHERE id_user_stalkers = ".$id."
                ) AS user_is_stalked

Just remove the last "FROM stalkers"

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

A safe way to set up this query for any database is to use a cross join:

SELECT user_stalkers, user_is_stalked
from (SELECT COUNT(id_user) as user_stalkers
      FROM   stalkers WHERE id_user =  ".$id."
     ) t1 cross join
     (SELECT COUNT(id_user_stalkers) as user_is_stalked
      FROM   stalkers WHERE id_user_stalkers = ".$id."
     )  t2

Your query is returning one row for every row in Stalkers because of the FROM clause.

Upvotes: 0

Robert
Robert

Reputation: 25753

You have to add one more where clause as below and distinct:

SELECT DISTINCT (
                    SELECT COUNT(id_user)
                    FROM   stalkers WHERE id_user =  ".$id."
                    ) AS user_stalkers,
                    (
                    SELECT COUNT(id_user_stalkers)
                    FROM   stalkers WHERE id_user_stalkers = ".$id."
                    ) AS user_is_stalked
FROM stalkers  
WHERE id_user_stalkers = ".$id."

Upvotes: 0

Related Questions