Sorting by a pre-sub-grouped number of records - HTML Forums

cmetz1977 · 08-02-2009, 01:12 PM

I have a MySQL query and I would like to sort by the total results of first item in the GROUP statement.

For example:

PHP Code:


			
SELECT `name`, `address`, count( `address` ) as 'count' FROM `table` GROUP BY `name`, `address`

Let's say there are 20 records for "John Smith", all with a unique address, and only 3 records for "Bob Miller", all with the same address. If I used ORDER BY `count` DESC at the end of my statement, "Bob Smith" would be listed first because "count" would be 3 where all the "count"s for "John Smith" would be 1.

Is there a statement that would allow me to rank "John Smith" higher in the sort since he has more overall records, while still letting me sub-group by address?

Horus_Kol · 08-02-2009, 06:40 PM

You can order by aggregate values:

PHP Code:


			
SELECT `name`, `address`, count( `address` ) as `count` FROM `table` GROUP BY `name`, `address` ORDER BY `count` DESC;

cmetz1977 · 08-03-2009, 11:39 AM

I'm getting a headache trying to think of this from all the angles. I think I am going to run

PHP Code:


			
SELECT `address`, count(`address` ) as 'count' FROM `table` GROUP BY `address` ORDER BY `count` DESC

then use that as a map for all the possible names at each address result.

Our clients will sometimes put in 'need address' or 'n/a', ... for their customer's addresses. We are trying to distinguish when multiple customers have the same address in situations like these, from companies that have multiple entries with the same name and same address. My problem doesn't just lie within this query but trying to design the entire interface and logic.