0

There is 2 table: "users" and "clicks".

Users have: 'username' and 'referrer' Clicks have: 'user' and 'value'

users.username = clicks.user

How can I SUM clicks.value of the users whos referrer is "janosuser"?

I know it's INNER Join but I don't know what is the sql.

Plus Insta
  • 13
  • 2

2 Answers2

0

This query will give you what you want:

SELECT SUM(clicks.value) FROM clicks INNER JOIN users ON users.username = clicks.user WHERE users.referrer = 'janosuser'
Muhammad Sumon Molla Selim
  • 3,914
  • 1
  • 24
  • 33
0

When you are not sure whether the username in users table is unique or not

SELECT sum(clicks.value) 
   FROM clicks 
   WHERE clicks.user IN 
       (SELECT users.username from users WHERE users.referrer = "janosuser")

Or

When you are sure that users table doesn't have repeated username, i.e. username is unique in the users table, otherwise for that users there will be repeated rows and sum will be inflated

SELECT SUM(clicks.value) 
    FROM clicks 
        INNER JOIN 
    users 
    ON users.username = clicks.user 
    where users.refere = "janosuser"
PRYM
  • 513
  • 4
  • 12
  • I get this message: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' – Plus Insta Jun 24 '16 at 17:46
  • in the INNER JOIN method? try adding 'COLLATE utf8_unicode_ci' after where users.referer = "Janosuser" Refer - http://stackoverflow.com/questions/11770074/illegal-mix-of-collations-utf8-unicode-ci-implicit-and-utf8-general-ci-implic – PRYM Jun 24 '16 at 17:53