Skip to main content

Group by and Having Clauses in SQL

To effectively work with SQL, it’s crucial to understand the logical order in which SQL executes its clauses. This understanding is essential for crafting queries that yield accurate and meaningful results. The logical execution order differs from the way queries are written, starting with the FROM clause, even though SELECT appears first in the syntax.

Logical Execution Order of SQL Clauses

The logical execution order is as follows:

Execution Order Clause
1 FROM
2 WHERE
3 GROUP BY
4 HAVING
5 SELECT
6 ORDER BY

Let’s use a real-world example involving sales data to illustrate these steps. Assume we have a table named sales_data with the following fields:

  • city: Name of the city
  • country: Country of the city
  • sales: Total sales in dollars

Step-by-Step Query Execution

Initial Data

The table sales_data contains the following data:

City Country Sales
New York USA 20,000
Los Angeles USA 15,000
Chicago USA 12,000
Beijing China 25,000
Shanghai China 18,000
Shenzhen China 10,000
Tokyo Japan 22,000
Osaka Japan 16,000

1. Filtering with WHERE

The WHERE clause filters cities with sales greater than 12,000:

SELECT city, country, sales
FROM sales_data
WHERE sales > 10000;

Result:

City Country Sales
New York USA 20,000
Los Angeles USA 15,000
Chicago USA 12,000
Beijing China 25,000
Shanghai China 18,000
Tokyo Japan 22,000
Osaka Japan 16,000

2. Grouping with GROUP BY

The GROUP BY clause aggregates the data by country:

SELECT country, SUM(sales) AS total_sales
FROM sales_data
WHERE sales > 10000
GROUP BY country;

Result:

Country Total Sales
USA 47,000
China 43,000
Japan 38,000

3. Filtering Groups with HAVING

The HAVING clause filters groups with total sales greater than 40,000:

SELECT country, SUM(sales) AS total_sales
FROM sales_data
WHERE sales > 10000
GROUP BY country
HAVING SUM(sales) > 40000;

Result:

Country Total Sales
USA 47,000

Key Takeaways

The WHERE clause filters rows before grouping, while the HAVING clause filters aggregated groups. Combining these effectively allows precise control over the data included in your query results.

This Week's Best Picks from Amazon

Please see more curated items that we picked from Amazon here .

Popular posts from this blog

Exploring Sentiment Analysis Using Support Vector Machines

Sentiment analysis, a powerful application of Natural Language Processing (NLP), involves extracting opinions, attitudes, and emotions from textual data. It enables businesses to make data-driven decisions by analyzing customer feedback, social media posts, and other text-based interactions. Modern sentiment analysis has evolved from simple rule-based methods to advanced machine learning and deep learning approaches that detect subtle nuances in language. As text communication continues to dominate digital interactions, sentiment analysis is an essential tool for understanding public opinion and driving actionable insights. The GoEmotions Dataset The GoEmotions dataset, developed by Google Research, is a benchmark in emotion recognition. It consists of over 67,000 text entries labeled across 27 emotion categories, such as joy, anger, admiration, and sadness. For practical applications, these emotions can be grouped into broader categories like positive and negati...
Read more

Autonomous Vehicles and AI Integration

Autonomous vehicles (AVs) represent one of the most transformative innovations of modern technology. These vehicles leverage artificial intelligence (AI) technologies to perform tasks traditionally carried out by human drivers, such as navigation, obstacle avoidance, and traffic management. The integration of AI into autonomous vehicle designs has enabled advancements in safety, efficiency, and convenience. This paper examines the current state of technologies involved in AV development, emphasizing the role of AI in supporting various vehicle functions and passenger needs. Additionally, it provides an overview of key organizations driving advancements in this field. AI Technologies Underpinning Autonomous Vehicle Development Artificial intelligence is central to the operation of autonomous vehicles, providing the computational foundation for critical capabilities such as perception, decision-making, and control. These capabilities are achieved through the integration of multiple t...
Read more

Predicting Algerian Forest Fires Using Regression Models

Forest fires are a growing global concern, causing environmental damage, threatening biodiversity, and endangering human lives. In Algeria, the Bejaia and Sidi-Bel Abbes regions face heightened risk due to rising temperatures, dry conditions, and strong winds. Predictive models can help forecast fire risks based on environmental factors, enabling early intervention strategies. This blog explores the use of linear regression to predict the Fire Weather Index (FWI) and logistic regression to predict fire occurrences. Using the Algerian Forest Fires Dataset, we analyze how temperature and wind speed influence fire risks and occurrences. Dataset Overview The Algerian Forest Fires Dataset includes data from two regions: Bejaia and Sidi-Bel Abbes. Key variables include: Temperature Relative Humidity (RH) Wind Speed (Ws) Fire Weather Index (FWI) Fire occurrence class ("fire" or "not fire") The da...
Read more