CODEWITHSUNDEEP
mysqlsql
genesis
genesis

Reputation: 50982

What's wrong on this query?

I'm selecting total count of villages, total count of population from my tables to build statistics. However, there is something wrong. It returns me everything (530 pop (there are 530 pop in total), (106 villages (there are 106 users in total)) in first row, next rows are NULLs

enter image description here

SELECT s1_users.id userid, (

SELECT count( s1_vdata.wref ) 
FROM s1_vdata, s1_users
WHERE s1_vdata.owner = userid
)totalvillages, (

SELECT SUM( s1_vdata.pop ) 
FROM s1_users, s1_vdata
WHERE s1_vdata.owner = userid
)pop
FROM s1_users
WHERE s1_users.dp >=0
ORDER BY s1_users.dp DESC

Upvotes: 0

Views: 110

Answers (4)

Samir Adel
Samir Adel

Reputation: 2499

SELECT userid,totalvillages,pop   from 
(
SELECT s1_users.id as userid, count( s1_vdata.wref ) as totalvillages
FROM s1_vdata, s1_users
WHERE s1_vdata.owner = userid
GROUP BY s1_users.id) tabl1 INNER JOIN
(
SELECT s1_users.id as userid, SUM( s1_vdata.pop )  as pop
FROM s1_users, s1_vdata
WHERE s1_vdata.owner = userid
GROUP BY s1_users.id) tabl2 on tabl1.userid = tabl2.userid

Upvotes: 0

Mchl
Mchl

Reputation: 62395

You're already using INNER JOINs. Whan you list tables separated with comma, it is a shortcut for INNER JOIN.

Now, the most obvious answer is that your subqueries using aggregating functions (COUNT and SUM) are missing a GROUP BY clauses.

SELECT s1_users.id userid, (

SELECT count( s1_vdata.wref ) 
FROM s1_vdata, s1_users
WHERE s1_vdata.owner = userid
GROUP BY s1_vdata.owner
)totalvillages, (

SELECT SUM( s1_vdata.pop ) 
FROM s1_users, s1_vdata
WHERE s1_vdata.owner = userid
GROUP BY s1_vdata.owner
)pop
FROM s1_users
WHERE s1_users.dp >=0
ORDER BY s1_users.dp DESC

However, using subqeries in column list is really inefficient. It casues subqueries to be run once for each row in outer query.

Try like this instead

SELECT 
  s1_users.id AS userid,
  COUNT(s1_vdata.wref) AS totalvillages,
  SUM(s1.vdata.pop) AS pop
FROM
  s1_users, s1_vdata  --I'm cheating here! There's hidden INNER JOIN in this line ;P
WHERE
  s1_users.dp >= 0
  AND s1_users.id = s1_vdata.owner
GROUP BY
  s1_users.id
ORDER BY
  s1_users.dp DESC

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425833

SELECT  s1_users.id AS userid,
        (
        SELECT  COUNT(*)
        FROM    s1_vdata
        WHERE   s1_vdata.owner = userid
        ) AS totalvillages,
        (
        SELECT  SUM(pop)
        FROM    s1_vdata
        WHERE   s1_vdata.owner = userid
        ) AS pop
FROM    s1_users
WHERE   dp >= 0
ORDER BY
        dp DESC

Note that this is less efficient than this query:

SELECT  s1_users.id AS user_id, COUNT(s1_vdata.owner), SUM(s1_vdata.pop)
FROM    s1_users
LEFT JOIN
        s1_vdata
ON      s1_vdata.owner = s1_users.id
GROUP BY
        s1_users.id
ORDER BY
        dp DESC

since the aggregation needs to be done twice in the former.

Upvotes: 0

Don
Don

Reputation: 17636

Try removing s1_users from inner SELECTS

Upvotes: 2

Related Questions