MYSQL Sum and Count on different tables, in same query

Question

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.

When you list the tables this way FROM studenkel_userprefs as p, studenkel_content as c, studenkel_users as u it will give you a Cartesian product for all the rows from the three tables, it is also called CROSS JOIN you should use INNER JOIN but you need to specify how the three tables relate to each other, please update your question and show how they are related. — Mahmoud Gamal
– Mahmoud Gamal, Commented Oct 22, 2013 at 15:17
That query will result in a cartesian join, you need to join your tables together. — Andrew
– Andrew, Commented Oct 22, 2013 at 15:17
use a proper JOIN syntax, and tell the DB HOW those tables relate to each other, e.g. in this exact case you need a where clause: ... WHERE studenkel_userprefs.foo = studenkel_content.bar AND etc.... — Marc B
– Marc B, Commented Oct 22, 2013 at 15:18

Florian F. · Accepted Answer · 2013-10-22 15:22:27Z

2

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;

answered Oct 22, 2013 at 15:22

Florian F.

4,70029 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

h2ooooooo · Accepted Answer · 2013-10-22 15:19:23Z

0

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`

answered Oct 22, 2013 at 15:19

h2ooooooo

39.6k8 gold badges70 silver badges107 bronze badges

Comments

Mike Brant · Accepted Answer · 2013-10-22 15:19:36Z

0

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.

answered Oct 22, 2013 at 15:19

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Collectives™ on Stack Overflow

MYSQL Sum and Count on different tables, in same query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related