MySQL: Count then sort by the count total

Question

I know other posts talk about this, but I haven't been able to apply anything to this situation.

This is what I have so far.

SELECT * 
FROM ccParts, ccChild, ccFamily
WHERE parGC = '26' AND 
  parChild = chiId AND 
  chiFamily = famId 
ORDER BY famName, chiName

What I need to do is see the total number of ccParts with the same ccFamily in the results. Then, sort by the total.

Could you share some sample data and the result you'd like to get for it? — Mureinik, Apr 25 '15 at 16:07
First of all you should not use SELECT * on three different tables but select only the field you need. Then, could you show us the structure of the tables? — Lelio Faieta, Apr 25 '15 at 16:07

score 1 · Answer 1 · answered Apr 25 '15 at 16:12

1

In the SELECT, add something like count(ccParts) as count then ORDER BY count instead? Not sure about the structure of your tables so you might need to improvise.

answered Apr 25 '15 at 16:12

Reuben L.

2,806
2
29
45

Ok, I think I need to use a group, so that I'm counting ccParts with the same ccFamily.? – Josiah Apr 25 '15 at 16:18
You will need to show some sample data. And yes, I think that a `GROUP BY` is necessary. – Reuben L. Apr 25 '15 at 16:19
1

You might want to look at this: http://stackoverflow.com/questions/2283305/mysql-order-by-count – Reuben L. Apr 25 '15 at 16:20

John Bollinger · Accepted Answer · 2015-04-25T16:31:05.507

It looks like this is close to what you want:

SELECT f.famId, f.famName, pc.parCount
FROM (
    SELECT c.chiFamily AS famId, count(*) AS parCount
    FROM 
      ccParts p
      JOIN ccChild c ON p.parChild = c.chiId
    WHERE p.parGC ='26' 
    GROUP BY c.chiFamily
  ) pc
  JOIN ccFamily f ON f.famId = pc.famId
ORDER BY pc.parCount

The inline view (between the parentheses) is the headliner: it does your grouping and counting. Note that you do not need to join table ccFamily there to group by family, as table ccChild already carries the family information. If you don't need the family name (i.e. if its ID were sufficient), then you can stick with the inline view alone, and there ORDER BY count(*). The outer query just associates family name with the results.

Additionally, MySQL provides a non-standard mechanism by which you could combine the outer query with the inline view, but in this case it doesn't help much with either clarity or concision. The query I provided should be accepted by any SQL implementation, and it's to your advantage to learn such syntax and approaches first.

MySQL: Count then sort by the count total

2 Answers2