When data is to be divided into groups by field values, the GROUP BY command is used:
SELECT
field_1,
field_2,
...,
field_n,
AGGREGATE_FUNCTION(field) AS here_you_are
FROM
table_name
WHERE -- if needed
condition
GROUP BY
field_1,
field_2,
...,
field_n
Once you know which fields you'll be grouping by, make sure all those fields are listed in both the SELECT block and the GROUP BY block. The aggregate function itself shouldn't be included in the GROUP BY block; otherwise, the query won't comply. SQL's GROUP BY operates much like the groupby() method in pandas.
GROUP BY can be used with any aggregate function: COUNT, AVG, SUM, MAX, MIN. You can call several functions at a time.
Analysis results are usually presented in a certain order. To sort data by a field, you use the ORDER BY command.
SELECT
field_1,
field_2,
...,
field_n,
AGGREGATE_FUNCTION(field) AS here_you_are
FROM
table_name
WHERE -- if needed
condition
GROUP BY
field_1,
field_2,
...,
field_n,
ORDER BY -- if needed. List only those fields
--by which the table data is to sorted
field_1,
field_2,
...,
field_n,
here_you_are;
Unlike GROUP BY, with ORDER BY, only those fields by which we want to sort the data should be listed in the the command block.
Two modifiers can be used with the ORDER BY command to sort the data in columns:
The ORDER BY modifiers are placed right after the field by which the data is sorted:
ORDER BY
field_name DESC
-- sorting data in descending order
ORDER BY
field_name ASC;
-- sorting data in ascending order
The LIMIT command sets a limit to the number of rows in the result. It always comes at the end of a statement, followed by the number of rows at which the limit is to be set (n):
SELECT
field_1,
field_2,
...,
field_n,
AGGREGATE_FUNCTION(field) AS here_you_are
FROM
table_name
WHERE -- if needed
condition
GROUP BY
field_1,
field_2,
...,
field_n,
ORDER BY -- if needed. List only those fields
--by which the table data is to sorted
field_1,
field_2,
...,
field_n,
here_you_are
LIMIT -- if needed
n;
-- n: the maximum number of rows to be returned
The WHERE construction is used to sort data by rows. Its parameters are, in fact, table rows. When we need to sort data by aggregate function results, we use the HAVING construction, which has a lot in common with WHERE: