CODEWITHSUNDEEP
phpmysqlsqlcountsum
eirik
eirik

Reputation: 101

MYSQL Sum and Count on different tables, in same query

Im trying to get some stats; total links, users and views, from 3 different tables. What im trying:

SELECT SUM(p.views), COUNT(c.id), COUNT(u.id)
FROM studenkel_userprefs as p, studenkel_content as c, studenkel_users as u

If I do them individually in three different queries, they work fine, but togheter the results gets a couple thousand times higher than what i want. I guess they multiply eachother in some way, Tips?

Thanks

Edit: Thanks guys, really appreciate your help, sorted it out.

Upvotes: 1

Views: 1666

Answers (3)

Florian F.
Florian F.

Reputation: 4700

What you're doing in your from is called implicit inner joins without criteria.

Which means your final view have p.rows * c.rows * u.rows rows and that's why you have weird result.

If you have 3 questions, ask them separately, not all at once.

Alternatively, if you really want only one request, you could go with something ugly as :

SELECT
  SELECT SUM(views) FROM studenkel_userprefs as "sum_userprefs",
  SELECT COUNT(id) FROM studenkel_content as "cnt_content",
  SELECT COUNT(u.id) FROM studenkel_users as "cnt_users"
FROM DUAL;

Upvotes: 2

Mike Brant
Mike Brant

Reputation: 71384

This is because you are making a Cartesian join of the tables, which multiplies the number of rows in one table times the number of rows in each other table. Without specifying a join in either JOIN ... ON format or by specifying the join criteria in WHERE clause, this is the only result you will get.

Upvotes: 0

h2ooooooo
h2ooooooo

Reputation: 39532

Are you allowed to "cheat"?

SELECT
    (
        SELECT 
            SUM(p.views)
        FROM 
            studenkel_userprefs as p
    ) as `views`,
    (
        SELECT
            COUNT(c.id)
        FROM
            studenkel_content as c
    ) as `content_count`,
    (
        SELECT
            COUNT(u.id)
        FROM
            studenkel_users as u
    ) as `user_count`

Upvotes: 0

Related Questions