Now that we have gone over functions, we can look at summarizing data or grouping data together. If you haven’t checked out functions yet, be sure to hit that page first.  While we have some functions that will look at the aggregate or whole of the data, what if we wanted to break it up a bit? For example, perhaps we are interested in finding the most common cat name in our system.

The idea of grouping is to identify how we are going to create our groups. In our cat name example, we may want to group or categorize the data by the column NameOfCat. We will add the keyword GROUP BY to our query and specify the name of the column we are grouping—NameOfCat. In my Select section, I will request two columns—the column I am grouping on and the function I need to count the number of names. I will also include the keyword ORDER BY so the data makes more sense. As I want to get the most popular cat name, I will order by the most popular first.

Select NameOfCat, COUNT(*) AS 'Number of Cats'
 FROM Cat
 GROUP BY NameOfCat
 ORDER BY COUNT(*) DESC;

OUTPUT (Modified)

NameOfCat        Number of Cats
————–             ————–

Bella                           7
Luna                           7
Olive                           6
Jax                               6
Milo                            5
Max                            4
Mittens                      4
Molly                          4
Leo                             4
Lily                             4

(111 row(s) affected)

 

The result is a list of cat names with the number of times they appear in the database. The names are listed in order of most popular to least popular.

In our database, it appears Bella and Luna are tied for first with 7 cats with that name.

Ultimately, the question we must ask ourselves is what do we want to get out of the data? We can use another of the functions we introduced earlier to find the number of pet owners by country in our database. With the goal of making the data more readable, we want to order by the count in descending order for easy lookup of the most records.

 

 

SELECT CountryOfResidence, COUNT(*) AS NumberOfOwners

FROM PetOwner

GROUP BY CountryOfResidence

ORDER BY COUNT(*) DESC;

OUTPUT (Modified)

 

CountryOfResidence                   NumberOfOwners
——————————                ——————–

United States Of America                57
Great Britain                                      48
Costa Rica                                          33
Singapore                                          31
Tanzania                                            23
Argentina                                           21
New Zealand                                     20
Peru                                                   18
Luxembourg                                     18
Kenya                                                 16

 

(27 row(s) affected)

 

Of course, we can group by more than one column—in fact, many times we will want to do this. Let’s take our name example a bit further and include the gender of the owner in the statement. Since Country is the component I want to see first, I list CountryOfResidence first in the GROUP BY section, followed by Gender.

SELECT CountryOfResidence, OwnerGender, COUNT(*) AS NumberOfOwners

FROM PetOwner

GROUP BY CountryOfResidence, OwnerGender

ORDER BY COUNT(*) DESC;

 

 

 

OUTPUT

(Modified)

CountryOfResidence      OwnerGender      NumberOfOwners
——————————         ———–                  ————–

United States of America         M                             36
Great Britain                               F                             25
Great Britain                              M                             23
United States Of America         F                              21
Costa Rica                                   F                              21
Singapore                                   F                              18
Tanzania                                    F                               16
Argentina                                   F                               13
Singapore                                 M                               13
Luxembourg                            M                              13

(44 row(s) affected)

 

A second twist I am putting on this query is I want to see the countries listed in alphabetical order, followed by the owner genders and then the number of owners. My ORDER BY clause has been updated to reflect this change. While the ASC is optional, I have included it to illustrate the logic of the query.

 

SELECT CountryOfResidence, OwnerGender, COUNT(*) AS NumberOfOwners

FROM PetOwner

GROUP BY CountryOfResidence, OwnerGender

ORDER BY CountryOfResidence ASC, COUNT(*) DESC;

 

OUTPUT

(Modified)

CountryOfResidence            OwnerGender    NumberOfOwners
——————————-       ———–             ———

Argentina                                 F                          13
Argentina                                 M                           8
Costa Rica                                F                          21
Costa Rica                               M                         12
Denmark                                  F                            2
Djibouti                                     F                           1
Djibouti                                     M                          1
Dominica                                  F                           2
Dominican Republic                F                          1
Dominican Republic                M                         1

(44 row(s) affected)