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

  1. Get all the campaigns.

      SELECT * FROM campaigns;
    
  2. Get how many clicks the campaign has:

    select SUM(id) as IALT2 from aktivitet where annonce_id = '@@CAMPAIGN_ID' group by ip");
    
  3. 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

Answers


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


Need Your Help

Pass objects to another template without saving first (django)

django

Is there a way to pass a newly created object(not saved yet) to another page?

How to get all users in a list Twitter API?

python api twitter tweepy

Is there a way to access all members in a list?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.