Support Online
MirrorLooking Glass
GenixNodeGenixNode
Client Panel
Skip to main content

Using SQL GROUP BY and ORDER BY

In this guide, you will learn the logic of grouping data and sorting results in SQL queries.
You will clarify the correct use of GROUP BY, ORDER BY, WHERE and HAVING statements.

Technical Summary​

Main topic: GROUP BY and ORDER BY in SQL
Purpose: To summarize and organize large data sets
Steps: Example table → GROUP BY → ORDER BY → Use together

Prerequisites​

  • MySQL 8.x or PostgreSQL
  • A working SQL database
  • Basic SELECT knowledge

Sample Dataset​

CREATE TABLE movie_theater (
  theater_id INT PRIMARY KEY,
  date DATE,
  time TIME,
  movie_name VARCHAR(40),
  movie_genre VARCHAR(30),
  guest_total INT,
  ticket_cost DECIMAL(4,2)
);
  • This table stores cinema screening data.

What is GROUP BY?​

  1. GROUP BY groups rows with the same values. It is often used with aggregate functions.

SELECT movie_genre, AVG(guest_total) AS ortalama
FROM movie_theater
GROUP BY movie_genre;
  • This query calculates the average number of viewers by genre.

Using COUNT with GROUP BY​


SELECT movie_name, COUNT(*) AS gosterim_sayisi
FROM movie_theater
GROUP BY movie_name;
  • This query returns the number of times each movie was shown.

Using SUM with GROUP BY​


SELECT date, SUM(guest_total * ticket_cost) AS toplam_gelir
FROM movie_theater
GROUP BY date;
  • This query calculates total ticket revenue by day.

Difference Between WHERE and HAVING​

WHERE → Filters before grouping

HAVING → Filters after grouping


SELECT movie_genre, AVG(guest_total) AS ortalama
FROM movie_theater
GROUP BY movie_genre
HAVING AVG(guest_total) > 100;
  • This query lists species with an average greater than 100.

DISTINCT and GROUP BY Comparison​


SELECT DISTINCT movie_name FROM movie_theater;
  • This query returns unique movie names.

SELECT movie_name FROM movie_theater GROUP BY movie_name;
  • This query produces the same result with GROUP BY.

➡️ If there is no collection, DISTINCT is preferred, if there is analysis, GROUP BY is preferred.​

What is ORDER BY?​

  • ORDER BY sorts the query results ascending or descending.

SELECT guest_total
FROM movie_theater
ORDER BY guest_total;
  • This query sorts the viewer counts from smallest to largest.

Using ORDER BY DESC​


SELECT movie_name
FROM movie_theater
ORDER BY movie_name DESC;
  • This query sorts movie titles from Z to A.

Using GROUP BY and ORDER BY together​


SELECT movie_name,
       SUM(guest_total * ticket_cost) AS toplam_gelir
FROM movie_theater
GROUP BY movie_name
ORDER BY toplam_gelir DESC;
  • This query sorts movies by total revenue.

Real Life BI Example


SELECT movie_genre,
       SUM(guest_total * ticket_cost) AS gelir
FROM movie_theater
WHERE time BETWEEN '17:00:00' AND '23:00:00'
GROUP BY movie_genre
HAVING SUM(guest_total) > 150
ORDER BY gelir DESC
LIMIT 5;
  • This query finds the most profitable genres in evening sessions.

Frequently Asked Questions (FAQ)​

1. Can SUM be used without GROUP BY? No. Grouping is required.

2. What is the default order ORDER BY? Ascending (ASC).

3. Can you use WHERE instead of HAVING? No. HAVING is for groups.

4. Does GROUP BY sort? No. Sorting is done by ORDER BY.

5. Where should ORDER BY be written? It is located at the end of the SQL query.

Common Mistakes​

❌ Adding columns other than GROUP BY to SELECT ❌ Using WHERE instead of HAVING ❌ Writing ORDER BY in the wrong order


-- Yanlış kullanım
SELECT movie_genre, movie_name, AVG(guest_total)
FROM movie_theater
GROUP BY movie_genre;

Result​

With this guide, you have clearly learned the logic of data grouping and sorting in SQL. GROUP BY performs analysis, ORDER BY organizes the results.

🚀 You can safely run these queries with high-performance databases on the GenixNode infrastructure.