Results 1 to 4 of 4

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

  1. #1
    SuperMod - OBDII GPS Logger forum
    Auto Apps:loading...

    Join Date
    Mar 2009
    Location
    Los Angeles
    Posts
    924
    Left join to get all the tables together. Exclude counts that are null.
    ... or, do an inner join?


    It seems that you're missing a developer table in your query. I'm also not sure why you don't count orders instead of downloads; otherwise I could cheese it with eighteen clicks of the download button?

    To do similar to what you'd do, why not just COUNT(developer.name) and skip the join against the actual downloads? Then you'd know how many separate orders were made for a developer's software

    Ignoring the fact that the developers table appears to be missing [unless "developer.name" is a column in your software table, which I doubt], this is probably significantly faster:
    Code:
    SELECT developer.name, COUNT(developer.name) as count
    FROM software
    INNER JOIN orders_software on software.id = orders_software.software_id
    GROUP BY developer.name
    ORDER BY count DESC
    LIMIT 11
    Gary (-;
    OBDGPSLogger, for logging OBDII and/or GPS data
    OBDSim, an OBDII/ELM327 software simulator
    mp3car forums: obdgpslogger, obdsim

  2. #2
    SuperMod - OBDII GPS Logger forum
    Auto Apps:loading...

    Join Date
    Mar 2009
    Location
    Los Angeles
    Posts
    924
    OK, I don't know how I managed to do that, but my reply appears to be above your original post... ?
    OBDGPSLogger, for logging OBDII and/or GPS data
    OBDSim, an OBDII/ELM327 software simulator
    mp3car forums: obdgpslogger, obdsim

  3. #3
    Antenna Engineer
    Auto Apps:loading...
    optikalefx's Avatar
    Join Date
    Apr 2009
    Location
    Baltimore, Maryland, United States
    Posts
    831
    Blog Entries
    86

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

    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

  4. #4
    Antenna Engineer
    Auto Apps:loading...
    optikalefx's Avatar
    Join Date
    Apr 2009
    Location
    Baltimore, Maryland, United States
    Posts
    831
    Blog Entries
    86
    IF one 1 order was 1 download yes that would work. But that is not the case. The same person can download software more than once without having to go through the whole buying process again, even if its free.

    I have a different query for unique downloads, but this will count all the total downloads of all software for each developer, then sort that by which developer has the most downloads. Yes you could pad your own stats, but maybe ill put some time constraint on clicking it a million times. But there will be cases when you need to download the software more than once, and we need to track that as another download.

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •