View RSS Feed

optikalefx

SQL with SUM, group, order, left join, exclude and limit

Rate this Entry
by , 05-13-2010 at 09:00 AM (1667 Views)
So I came across a scenario where I need to know the top developers of applications based on how many times their software has been downloaded.

There are 3 tables involved here.
The developers table
the orders_software table
the orders_software_downloads table

The whole goal is get this information with 1 Sql query.The basic relationships are as follows:
there are many developers in the developer table
there are many softwares to 1 order in the orders_software table
there are many downloads to 1 software to 1 order in the orders_software_downloads table.

So we select the developer name from the developer table, and the download count from the orders_software_downloads table. Left join to get all the tables together. Exclude counts that are null. Then group the results by the owner of each software. Which is a row of the software table.

This will be a great case to use SUM

we can modify the download count to sum(download_count) and as long as we have that group by, it will sum up the group.



So sql will look like this:

SELECT developer.name, SUM(orders_software_download.download_count) as sum
FROM software
LEFT JOIN orders_software on software.id = orders_software.software_id
LEFT JOIN orders_products_download on orders_products.orders_software_id = orders_products_download.id
WHERE orders_software_download.download_count IS NOT NULLGROUP BY software.software_owner
ORDER BY sum DESC
LIMIT 11

And then from this the result will give you an array of the top developers where you can know who the developer is an how many times in total their software was downloaded

Submit "SQL with SUM, group, order, left join, exclude and limit" to Digg Submit "SQL with SUM, group, order, left join, exclude and limit" to del.icio.us Submit "SQL with SUM, group, order, left join, exclude and limit" to StumbleUpon Submit "SQL with SUM, group, order, left join, exclude and limit" to Google

Updated 05-13-2010 at 11:01 AM by optikalefx

Categories
Products and Technology

Comments

Leave Comment Leave Comment
View Thread

Last 3 Posts

Pooh, I didn't realize I was a mindless drone.. Thanks for filling me in!

Sent from my iPad.
Oooh, I didn't realize I was a mindless drone. Thanks for filling me in!

Sent from my iPad.
Quote Originally Posted by bigguy View Post
I am getting one for my wife for her birthday, when i do i want ot make a mount for it to go in my car just cause and see how it works. I have a Scion XB and i think i will make something that comes out of the storage pocket so it cna sit there
Be sure to check out http://ram-mount.com/
They have virtually every type of vehicle mount for every type of electronic device you can think of. And a litte inside scoop, when you buy a product that has a good steel/aluminum mount with it or sold sepereatly, they probably just bought it from here and are reselling it to you at a higher price, with their logo on it of course.