homepage Welcome to WebmasterWorld Guest from 54.196.63.93
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#: 4133548 posted 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

WebmasterWorld Senior Member whoisgregg us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4133548 posted 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

5+ Year Member



 
Msg#: 4133548 posted 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