1 Replies - 976 Views - Last Post: 10 July 2012 - 10:00 AM

#1 Fretwise  Icon User is offline

  • New D.I.C Head

Reputation: 6
  • View blog
  • Posts: 35
  • Joined: 15-February 09

Question regarding sorting by count

Posted 10 July 2012 - 09:32 AM

Hello, I have a simple database with store names and the order number that was placed with them. Here is an example
store         order
---------------------

Sears         25
Sears         36
Dillards      70
Dillards      12
Dillards      80
Wal-Mart      10


I am looking to display them as followed:
store         order
---------------------

Dillards      70
Dillards      12
Dillards      80
Sears         25
Sears         36
Wal-Mart      10


I have tried using queries such as:
SELECT COUNT(*), store, order FROM table GROUP BY store ORDER BY COUNT(*) 

which gives me.
store         order
---------------------

Dillards      3
Sears         2
Wal-Mart      1


I am looking to have them ordered in that fashion but also showing each order number. Any tips advice or help is greatly appreciated.

Is This A Good Question/Topic? 0
  • +

Replies To: Question regarding sorting by count

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5931
  • View blog
  • Posts: 12,853
  • Joined: 16-October 07

Re: Question regarding sorting by count

Posted 10 July 2012 - 10:00 AM

Your query shouldn't work, because "order" is not in group. Btw, I'd stop right now and choose another name for that field. Maybe order_num. Order will give you pain as a reseved word.

So, the group by might look like:
SELECT store, COUNT(*) as order_qty
	FROM table
	GROUP BY store



Now, try this:
SELECT *
	FROM table a
		INNER JOIN (
			SELECT store, COUNT(*) as order_qty
				FROM table
				GROUP BY store
		) b
			ON a.store = b.store



From there, you can choose the fields you want and order by order_qty.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1