SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Only one table but tricky (for me) Query

    Hi,

    Sample data
    name | Price | Code
    red | 1.50 | 100
    red | 1.75 | 100
    blue | 1.70 | 150
    yellow | 2.00 | 150

    I need a query that will group by code and count amount of results with the same code and show the results with the cheapest price like

    Name | Price | Code | Count
    red | 1.50 | 100 | 2
    blue | 1.70 | 150 | 1
    yellow | 2.00 | 150 | 1

    Can someone point me in the right direction please?

    Thanks

  2. #2
    SitePoint Addict
    Join Date
    Dec 2007
    Posts
    358
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select name, mix(price), code, count(*)
    from table
    group by name, code
    Last edited by alex.w747; Mar 4, 2009 at 16:43. Reason: Thanks to r937 for correcting me :-)
    I'm creating trouble-free Apache, PHP, MySQL installer, WITSuite,
    and use it to setup my development environment.
    Demo, support, contact. Questions?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    nice one alex -- but he'll want MIN instead of MAX
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's great alex, thanks.

    Doolally

  5. #5
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Looking again at this, the solution is not quite right, It's my fault I over simplified the example.

    There are a few more fields:

    supplier | link | name | Price | Code
    colour world |colourworld.com/red| red | 1.50 | 100
    colour city|colourcity.com/red| red | 1.75 | 100
    colour world |colourworld.com/blue| blue | 1.70 | 150
    colour city|colourcity.com/yellow| yellow | 2.00 | 150

    using the above solution i'd get the result
    colour city|colourcity.com/red| red | 1.50 | 100
    colour world |colourworld.com/blue| blue | 1.70 | 150
    colour city|colourcity.com/yellow| yellow | 2.00 | 150

    Where it does not match the cheapest price to the supplier. looking around (this thread) it is because the other fields are not in the group by clause.

    As all rows are unique, adding the rest of the fields to the group by clause I lose the count on the code, well not lose it but all results unique ie the count is 1

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT t.supplier
         , t.link
         , t.name
         , t.price
         , t.code
         , m.price_count
      FROM ( SELECT name
                  , code
                  , MIN(price) AS min_price
                  , COUNT(*) AS price_count
               FROM daTable
             GROUP
                 BY name
                  , code ) AS m
    INNER
      JOIN daTable AS t
        ON t.name  = m.name
       AND t.code  = m.code
       AND t.price = m.min_price
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much rudy, are these sorts of queries discussed in your book? the other sql books i've got do not touch on these sorts of queries, just basic selects, etc

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    this one ("row holding the groupwise max") isn't covered, but similar queries, yes
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy, I'll definitely be buying your book.

    One more thing, I've got about 50k rows and the query takes about 5 seconds, I've tried adding an index to various columns and it hasn't made much difference, could it be sped up with an index on the right column?

    Cheers

  10. #10
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The 5 seconds mentioned above is with a index on code and a fulltext index on name

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    replace the index on code with a composite index on code and name

    why the fulltext index on name? is there perhaps more in that column than red, yellow and blue?

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes names of dvds, so nothing to long is full text overkill?

  13. #13
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've just take off fulltext and put a normal index on name and it's gone up to about 25 seconds

  14. #14
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry rudy, somehow missed the first line of your post I'll try that now

  15. #15
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    rudy,

    Ok, with only the composite index on code and name it takes about 20-30 seconds
    I should mention I had to add another group clause as it was still returning rows with duplicate codes, this is my full query

    Code:
    SELECT t.dvd_supplier
         , t.dvd_link
         , t.dvd_name
         , t.dvd_price
         , t.dvd_code
         , m.price_count
      FROM ( SELECT dvd_name
                  , dvd_code
                  , MIN(dvd_price) AS min_price
                  , COUNT(*) AS price_count
               FROM dvds
             GROUP
                 BY dvd_name, dvd_code ) AS m
    INNER
      JOIN dvds AS t
        ON t.dvd_name  = m.dvd_name
       AND t.dvd_code  = m.dvd_code
       AND t.dvd_price = m.min_price
    GROUP BY t.dvd_code
    Apart from the links (which would have affiliate id's in, so have taken them out) this is a real sample of data

    Code:
    dvd_supplier 	dvd_link 	dvd_name 	dvd_price 	dvd_code 
    "Play.com";"http://www.play.com";"Mission Impossible: Special Collector's Edition (2 Discs)";"6.99";"5014437895639"
    "Play.com";"http://www.play.com";"Deception";"12.99";"5017239151019"
    "Play.com";"http://www.play.com";"Dancing On Ice: Vol.2";"4.99";"5037115244437"
    "Play.com";"http://www.play.com";"Fishtales";"6.99";"5037899006047"
    "Play.com";"http://www.play.com";"A Farewell To Arms (Studio Classics)";"4.99";"5039036020237"
    "Play.com";"http://www.play.com;"The Simpsons: Complete Season 9";"31.99";"5039036029544"
    "Play.com";"http://www.play.com";"Hellboy Animated: Blood And Iron";"3.99";"5060020625879"
    "Play.com";"http://www.play.com";"Peep Show: Series 1 - 4 Box Set (4 Discs)";"34.99";"6867441015796"
    "hmv";"http://www.hmv.com";"Lost In The Snow";"4.99";"5012106930117"
    "hmv";"http://www.hmv.com";"Snow White";"4.99";"5012106930193"

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    if you have two dvds which have the same minimum price, then the real solution is to show them both, ~not~ just add another GROUP BY to the query!

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just to update,

    I've had a good search around and it seems like the sort of query I wanted to do, just does take a long time. So I've cheated with
    Code:
    INSERT INTO `list` (`dvd_supplier`, `dvd_link`, `dvd_name`, `dvd_price`, `dvd_code`, `dvd_image`, `count`) (
    SELECT t.dvd_supplier
         , t.dvd_link
         , t.dvd_name
         , t.dvd_price
         , t.dvd_code
         , t.dvd_image
         , m.dvd_count
      FROM ( SELECT dvd_name
                  , dvd_code
                  , MIN(dvd_price) AS min_price
                  , COUNT(*) AS dvd_count
               FROM dvds
             GROUP
                 BY dvd_name, dvd_code ) AS m
    INNER
      JOIN dvds AS t
        ON t.dvd_name  = m.dvd_name
       AND t.dvd_code  = m.dvd_code
       AND t.dvd_price = m.min_price
    GROUP BY t.dvd_code
    The information in dvds will only update once or twice a week or so. I just need to run the above once dvds is updated and work of list

    Thanks again for all your help with this rudy

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by doolally View Post
    So I've cheated with ...
    that's not a cheat!!

    a long running-query that is only needed once a week is a perfect candidate for that strategy

    but i have to say, that with your GROUP BY, you run the risk of getting a row back where the supplier, link, or image might not all be from the same detail row -- i.e. different suppliers

    just a caution

    see http://dev.mysql.com/doc/refman/5.0/...n-columns.html
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •