0

I'm getting some unwanted results when grouping by two columns in SQL, here is the code;

SELECT i.*, o.mdate, u.name as producer_name, b.price as beat_price, COUNT(i.id) as sold_count
                FROM #__mfs_items AS i
                INNER JOIN #__mfs_users AS mu
                ON i.producer = mu.id
                INNER JOIN #__users AS u
                ON i.producer = u.id
                INNER JOIN #__mfs_beats AS b
                ON i.beat_id = b.id
                INNER JOIN #__mfs_orders AS o
                ON i.order_number = o.order_number              
                GROUP BY i.producer
                ORDER BY COUNT(*) DESC
                LIMIT 10

Now what this does is get the top selling items and group them by their sellers. This unfortunately gathers every sale of every item for each seller and totals them, which is not what I want. When I try with GROUP BY i.beat_id this gives me the desired results but there is one seller who takes all 10 top spots. When I try GROUP BY i.beat_id, i.producer - this gives me the exact same results as grouping by i.beat_id alone.

My aim is to have top selling items, but I want a unique seller for each spot. Can someone help me achieve this?

Thanks very much in advance

Wasim
  • 4,953
  • 10
  • 52
  • 87
  • Another "Top N / Group" problem. http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group has some information on what you need to get started. – Yuck Dec 09 '11 at 14:50

1 Answers1

0

You should have all the select columns in your group by condition when you are grouping them,Not a single column.

Teja
  • 13,214
  • 36
  • 93
  • 155