Only one table but tricky (for me) Query

[doolally]

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

[alex.w747]

select name, mix(price), code, count(*)
from table
group by name, code

[r937]

nice one alex -- but he'll want MIN instead of MAX

[doolally]

That's great alex, thanks.

Doolally

[doolally]

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

[r937]

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

[doolally]

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

[r937]

this one ("row holding the groupwise max") isn't covered, but similar queries, yes

[doolally]

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

[doolally]

The 5 seconds mentioned above is with a index on code and a fulltext index on name

[r937]

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?

[doolally]

yes names of dvds, so nothing to long is full text overkill?

[doolally]

I've just take off fulltext and put a normal index on name and it's gone up to about 25 seconds

[doolally]

Sorry rudy, somehow missed the first line of your post I'll try that now

[doolally]

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

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

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"

[r937]

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!

[doolally]

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

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

[r937]

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