Select from 3 tables with calculating
I try to make a query in MySQL that gets data from 3 tables, and calculate in 2 tables. But i really don't know how to do this.
I have this in my PHP code to calculate "Avage Cost per Click":
Get all the campaigns.
SELECT * FROM campaigns;
Get how many clicks the campaign has:
select SUM(id) as IALT2 from aktivitet where annonce_id = '@@CAMPAIGN_ID' group by ip");
Get the total revenure from all rows
SELECT SUM(price) as IALT from money where ad = '@@CAMPAIGN_ID' group by id
Now i can calculate the "Avage Cost Per Click"
"IALT / IALT2" = CPC
then it should say:
select * from campaign order by CPC desc limit 0,1
select * from campaigns C order by ( (select SUM(price) from money where ad = C.CAMPAIGN_ID) / (select SUM(id) from aktivitet where annonce_id = C.CAMPAIGN_ID) )desc limit 0,1
just guessing, but I think you want this