|Mysql - How to Sum Alias |
I have 4 tables.
Hits - date, advtid, productid
Lead requests - date, advtid, productid
Advertisers - id
products - id
How do I list all products for a selected advertiser sorted by total of hits + leads requests for a specific product for a specific range of dates?
So if there are 10 products.
It should list all 10 products sorted by total activities
Total activities = total of hits + total of lead_requests
I am finding this very challenging. Any body can help?
Welcome to WebmasterWorld, osc2000!
Could you post your query so far? It's always helpful for us to have a starting point to work from. :)
SELECT a.advertiser_column_1, a.advertiser_column_2, p.product_column_1, (h.count + l.count) as total
FROM (SELECT count(*) as count, productid, advtid FROM Hits WHERE [date range clause] group by productid,advtid) h
inner join (SELECT count(*) as count, productid, advtid FROM Lead_requests WHERE [date range clause] group by productid,advtid) l
on (h.productid = l.productid and h.advtid = l.advtid)
inner join products p on h.productid = p.id
inner join advertisers a on h.advtid = a.id
[advertiser id clause]
ORDER BY total ASC
1. You don't have to do joins to the products and advertiser tables if you just want the IDs.
2. The advertiser ID clause can be inserted along with the date range clause. This would be good to do if there is a large amount of advertisers ("large" in the database language meaning - ie. 10 000).
3. You might not want to group by advtid in the nested queries and have advtid in the join clause ("h.advtid = l.advtid"), but I assumed that multiple advertisers can have the same product. If that's never the case you can remove those for a slight increase in performance.
I'm not 100% sure of this query, nor am I an SQL pro, but it should work. I am also thinking in MSSQL not MySQL, but on this level of SQL I'm almost certain they will be the same. Also - MySQL below version 4.something.something doesn't support nested queries. If your version doesn't support them - you're screwed.
And finally - this might not be the best approach. As I said - I'm not a pro but a frequent user and, as such, I make queries the way I see fit, not "The Ultimate, Best And Only Way".