I have a mySql database and I have a question about counting several values with the same "Provider_id". Let me explain it as simple as possible. I have 3 rows: Provider_id (No unique values at end of text), click_count(How many clicks on their site, int value) and logo_id(This one is the same as Provider_id so they could be connected). "Provider_id" and "click_count" are in the same table and logo_id is from a different table. I want to match "Provider_id" and "Logo_id" and want to count click_count with the matching Provider_id.
For example:
(table 1)
Provider_id = 102 click_count = 10
Provider_id = 102 click_count = 22
(table 2)
logo_id = 102
-- What I want to get --
Provider_id = 102 click_count = 32
Could anyone help me with this?
Explained Provider_id:
There are unique website id's in the table with Provider_id so the Provider_id doens't have to be unique.
What you can do is join both tables and do group by Provider_id and taking the sum of click_count will solve the problem.So try like this
select t1.Provider_id,sum(t1.click_count) from table_1 as t1
inner join table_2 as t2 on t1.Provider_id = t2.logo_id
group by t1.Provider_id
You can use JOIN statement and group by Provider_id:
SELECT b.logo_id, SUM(a.click_count) AS click_count
FROM table_1 AS a LEFT JOIN table_2 AS b ON a.Provider_id = b.logo_id
WHERE b.logo_id = 102
GROUP BY a.Provider_id;
Use
SELECT `field`,
count(*) as `count`
or
SELECT `field`,
SUM(`counter`) as `total`
together with
GROUP BY `field`
and don't forget to make INDEX(field) (or better INDEX(field, counter))