homepage Welcome to WebmasterWorld Guest from 54.237.184.242
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Mysql - How to Sum Alias
Mysql Query
osc2000




msg:4133474
 9:03 am on May 16, 2010 (gmt 0)

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?

 

whoisgregg




msg:4136018
 4:59 pm on May 20, 2010 (gmt 0)

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. :)

Furiat




msg:4140947
 10:43 pm on May 26, 2010 (gmt 0)

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
WHERE
[advertiser id clause]
ORDER BY total ASC

Possible tweaks:
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".

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved