Subqueries with GROUP BY in SQL – Advanced Query Techniques

Introduction

In SQL, text data types are used to store alphanumeric values like names, addresses, emails, and descriptions. Choosing the correct text type — CHAR, VARCHAR, or TEXT — is important for optimizing storage space, query speed, and database performance.

In this section, you'll learn the definitions, differences, and best use cases for each text data type.

1. CHAR (Fixed-Length String)

CHAR is used to store fixed-length strings. If the stored string is shorter than the defined length, SQL automatically pads it with spaces to match the specified size.

Features:

• Fixed length
• Fast and predictable performance
• Uses extra storage if the data is often shorter than the specified length

Syntax:

column_name CHAR(length);

length = number of characters (1 to 255 depending on the database system)

Example:

CREATE TABLE countries (
country_code CHAR(2),
country_name CHAR(50)
    );

country_code like 'US', 'IN', 'UK' will always take 2 characters.

When to Use CHAR:

• Data with a constant size, such as country codes, gender ('M', 'F'), state abbreviations
• Fixed-format fields like credit card types ('VISA', 'MC')
• When exact storage size is known and consistent

2. VARCHAR (Variable-Length String)

VARCHAR stands for Variable Character. It stores variable-length strings, meaning only the actual characters are stored without unnecessary padding.

Features:

• Variable length
• More space-efficient than CHAR for varying-length text
• Slightly slower than CHAR when processing large volumes (because of extra calculations for string lengths)

Syntax:

column_name VARCHAR(length);

length = maximum number of characters allowed

Example:

CREATE TABLE employees (
first_name VARCHAR(50),
email VARCHAR(100)
    );

Names and emails can vary in length, making VARCHAR ideal.

When to Use VARCHAR:

• Data with unpredictable or variable length
• Names, emails, addresses, and descriptions under 255-65535 characters
• Most general-purpose text fields

3. TEXT (Large Text Field)

TEXT is used to store large amounts of text like long descriptions, blog posts, comments, or articles.

Features:

• Meant for large text storage (up to 65,535 characters for standard TEXT in MySQL)
• Cannot have a default value (in some databases like MySQL)
• TEXT fields are stored outside the main table with a pointer reference
• Different variants exist (TINYTEXT, MEDIUMTEXT, LONGTEXT) for various sizes

Syntax:

column_name TEXT;

Example:

CREATE TABLE articles (
id INT,
title VARCHAR(255),
body TEXT
   );

body will store the full article content, which can be very large.

When to Use TEXT:

• Long-form text fields (comments, articles, reviews, reports)
• Data that exceeds normal VARCHAR limits
• When exact storage requirements are unknown or potentially very large

Quick Comparison: CHAR vs VARCHAR vs TEXT

Important Tips

• Use CHAR only when all values will be exactly the same length
• VARCHAR is the best choice for most standard text fields
• Reserve TEXT for content that exceeds VARCHAR limits
• Consider VARCHAR(MAX) in SQL Server for large text that might need indexing
• Be aware that TEXT fields may have limitations on default values and full-text indexing

What Are Subqueries?

A subquery (also called an inner query or nested query) is a query inside another query. When combined with GROUP BY, subqueries help to:

• Summarize data from a related table
• Filter or compare grouped results
• Build advanced reports using multi-level logic

Syntax – Subquery Inside FROM

SELECT column, AGG_FUNC
FROM (
  SELECT column, AGG_FUNC
  FROM table
  GROUP BY column
) AS sub
WHERE condition;

Use Case 1: Grouping in a Subquery and Filtering in Outer Query

Let's say you want to find customers who have spent more than $500 total:

SELECT customer_id, total_spent
FROM (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
) AS customer_summary
WHERE total_spent > 500;

The subquery groups and sums the orders, then the outer query filters the results.

Use Case 2: Subquery in SELECT With Grouped Outer Query

You can use a subquery to fetch a related value in each grouped row:

SELECT 
  customer_id,
  COUNT(*) AS order_count,
  (SELECT MAX(amount) FROM orders o2 WHERE o2.customer_id = o1.customer_id) AS max_order
FROM orders o1
GROUP BY customer_id;

This gets each customer's order count and their largest order value.

Use Case 3: Subquery as a Temporary Grouped Table

You can join a grouped subquery with another table for richer analysis:

SELECT c.name, order_summary.total
FROM customers c
JOIN (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
) AS order_summary ON c.id = order_summary.customer_id;

This produces customer names along with total spending, by joining a grouped subquery.

Why Use Subqueries with GROUP BY?

• Break down complex logic into manageable steps
• Avoid repeating aggregation logic
• Improve query modularity and readability
• Enable advanced filters not possible in a single level

Tips for Using Subqueries with GROUP BY

• Always alias subqueries (e.g., AS summary)
• Make sure outer queries reference correct column names
• Use HAVING in subqueries for aggregated filters
• Optimize performance by indexing join columns