Plus Insta Plus Insta - 3 months ago 12x
SQL Question

PHP MySql: SUM table value Where="from-other-table"? (INNER JOIN)

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.


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")


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 
    ON users.username = clicks.user 
    where users.refere = "janosuser"