[Fretwise]

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.

[baavgai]

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.