CODEWITHSUNDEEP
mysqljoincountalphabetical
Harry
Harry

Reputation: 109

MySQL joining tables and sorting data by count and alphabetical order

Let's say I had these two tables and wanted to write a query that displayed catType name and number of cats in each CatType for all CatTypes that are in my CatType table. I want to display the cats in a descending order but if two types of cat have the same number of cats then I want to display those in alphabetical order.

Here is what I have so far but I'm having trouble getting exactly what I want.

Any hints or things to think about would be super helpful. thanks. 

SELECT CatType_name AS 'Cat Type Name',
COUNT(*) AS 'Number of Cats' 
FROM CatType INNER JOIN Cats
ON Cats.CatType_id = Cats.CatType_id 
GROUP BY Cats.CatType_id, CatType_Name
ORDER BY CatType_name DESC;  

     Cats
---------------------
|Cat_id  | integer  | 
---------------------
|cat_name|  string  | 
---------------------
|cat_type | character|                   
---------------------
|CatType_id| integer|          
---------------------

     CatType
---------------------
|CatType_id|integer  | 
---------------------
|CatType_name|string | 
---------------------

Upvotes: 0

Views: 469

Answers (1)

vatsal mevada
vatsal mevada

Reputation: 5636

This might work. havent tested though.

SELECT CatType_name AS 'Cat Type Name', COUNT(*) AS 'Number of Cats'
     FROM CatType 
    INNER JOIN Cats 
    ON Cats.CatType_id = Cats.CatType_id
     GROUP BY Cats.CatType_id, CatType_Name 
ORDER BY count(*) DESC, CatType_name DESC;

Upvotes: 2

Related Questions