how do you sort with sql table-CodePudding

I need a sql table which has

CREATE TABLE IF NOT EXISTS fruit_eaten (
    eater_name varchar(255) NOT NULL,
    fruit_name varchar(255) NOT NULL
    )"""

The table will print out ('bob', 'Apple') ('bobby', 'Orange') ('bobbi', 'Apple') ('boy', 'Grape') ('bobthebuilder', 'Orange')

My question is

How do you make a new table that sort the fruit as its first columns and the amount the fruit is choosen in the second column

The second table will be (Apple, 2) (Orange, 2) (Grape, 1)

And when I put a new data it automatically enter the database and the largest amount will go to the highest If i put a new data on the table ('Bobushka', 'orange')

the new second column will be (Orange, 3) (Apple, 2) (Grape, 1)

I tried using sorting but i still don't really understand how, thank you

CodePudding user response：

Tables are unordered datasets and for your requirement you should not create a new table.

You need a query that aggregates with COUNT() and sorts the results properly, first by the result of COUNT() descending and then by the fruit's name (just in case COUNT() returns ties):

SELECT fruit_name,
       COUNT(*) AS number_of_eaters
FROM fruit_eaten
GROUP BY fruit_name
ORDER BY number_of_eaters DESC, fruit_name;

See the demo.