How to perform a query to return the number of rows that fulfils each column criteria

Question

I have a table called Bookings and the schema is as follows;

id | name | pizza     | country

1  | catA | pepperoni | USA
2  | catB | Sicilian  | USA
3  | catA | pepperoni | Greece
4  | catA | Sicilian  | Greece

How can I perform a query such that it returns for me the number of rows each value in column appear.

Example;

name
catA | 3
catB | 1

pizza
pepperoni | 2
Sicilian | 2

country:
USA | 2
Greece | 2

Answer 1

I believe you need to use a group by statement for this type of query. https://www.w3schools.com/sql/sql_groupby.asp

A group by statement lets you use aggregate functions such as COUNT().

SELECT name, COUNT(name) FROM Bookings GROUP BY name;

SELECT pizza, COUNT(pizza) FROM Bookings  GROUP BY pizza;

SELECT country, COUNT(country) FROM Bookings  GROUP BY country;

If you would like the output of all queries to be in a single result:

SELECT name, COUNT(name), 'name' FROM Bookings GROUP BY name
UNION
SELECT pizza, COUNT(pizza), 'pizza' FROM Bookings  GROUP BY pizza
UNION
SELECT country, COUNT(country), 'country' FROM Bookings  GROUP BY country;