Data Analysis Self-Study Day #1: Basic SQL Functions

2 min readFeb 17, 2025

Today’s Key Learning Points

General Structure of SQL Queries

SELECT
FROM
WHERE
GROUP BY
ORDER BY

Functions I should remember:

replace
substring / substr
concat
if
distinct
case when end
having

Challenges & Errors Encountered During Learning

Description of the Issue/Error:

Finding the number of redundant names:
While trying to filter the results so that I can only see the rows of redundant animal names, I faced an error while using the where and count function.

SELECT
    name,
    count(name) as "count"
FROM
    animal_ins
WHERE
    name is not null
    and (count(name) > 1)
GROUP BY
    name
ORDER BY
    name

The reason why an error occurs with this code in MySQL is because one cannot use an aggregate function like “count” under the “where” clause.

But what exactly is the logic behind this? It’s all about the way SQL processes its queries.

Since “count” functions are processed after the rows are grouped, and the “where” clause is evaluated before grouping, the process simply doesn’t match if I put “count” directly under the “where” clause.

In short, understanding the order SQL processes each function is as important as understanding how to use it on the surface level.

What I Tried & How I Solved It:

To resolve the issue above, I found out that I can use the “having” function, as below:

SELECT
    name,
    count(name) as "count"
FROM
    animal_ins
WHERE
    name is not null
GROUP BY
    name
HAVING
    count(name) > 1
ORDER BY
    name

As the “having” function is specifically designed to filter grouped data based on aggregate functions, we can use it in this case.

New Insights Gained:

Before filtering, think about what functions I need and evaluate where in the SQL workflow I can use it.
Remember that when filtering grouped data by aggregate functions, you can use the “having” function!
Keep coding and facing different problems to toughen up problem-solving skills.

What I Plan to Learn Tomorrow

Finish the MySQL basic courses and revise what I learned so far
Solve more coding test questions to keep myself from forgetting anything
Research more into what factors I need to become a data scientist