Sql subquery with COUNT help

sql

subquery

count

performance

byNikita Barsukov·Aug 24, 2024

Need a count per category in SQL? Join a subquery to the main table. For instance, get the number of orders per customer using:

SELECT 
  c.name, 
  COALESCE/*(Null busters, assemble!🦸)(This replaces NULL values with 0)*/(o.order_count, 0) AS order_count
FROM 
  customers c
LEFT JOIN (
  SELECT 
    customer_id, 
    COUNT(*)/*(Feels like an election count, right?😉)*/ AS order_count
  FROM 
    orders
  GROUP BY 
    customer_id /*(Who voted for who?)*/
) o ON c.id = o.customer_id;

This combines a grouped subquery count with the main query using a LEFT JOIN to ensure all customers (including those without orders) are reflected.

Window functions: A loophole in the GROUP BY maze! Where an overall count is needed without the GROUP BY clause, window functions are your secret weapon.

SELECT
  customer_id,
  COUNT(*) OVER()/*(Peekaboo! No GROUP BY here✌️)*/ AS total_orders
FROM orders
WHERE columnName = 'Business';

Advanced scenarios and performance

Count: Dead easy

Sometimes, you just need a count. Why complicate things with a subquery?

SELECT 
  columnName,
  COUNT(*)/*(No magnifying glass needed🔍)*/ AS row_count
FROM 
  eventsTable 
WHERE 
  columnName = 'Business';

It's like asking everyone in the room who likes pizza. Instant headcount.

Performance: It's a marathon, not a sprint

Big datasets? Complex queries? Subquery performance can get tricky. Indexing the right columns and understanding your database's optimizer can make or break your query execution time. Consider temporary tables or a common table expression (CTE) for large datasets. It's like upgrading your car for a long race🏎️.

Visualization

Let's boil down the SQL subquery with COUNT to its basics. Imagine you’re highlighting (💡) specific words in a book (📖):

Book Content (📖): [Word1, Word2, Word3, Word2, Word3, Word3]

The job is to count the appearance of Word3:

SELECT COUNT(Word) FROM (SELECT Word FROM 📖 WHERE Word = 'Word3') AS Subquery;

Occurrences of 🎯 (Word3):
Before: [Word1, Word2, Word3, Word2, Word3, Word3]
After:  💡💡💡
COUNT:  **3**

Just like a bingo game. Each 💡 equals one 'Word3' spot by the subquery.

Detailed counts: It’s all in the details

Counting occurrences is one thing. But what if you need to break them down even further?

SELECT
  Category,
  (SELECT COUNT(*) FROM Products WHERE Products.Category = CategoryList.Category) AS ProductCount
FROM 
  (SELECT DISTINCT Category FROM Products) AS CategoryList;

It’s like a game of trivia, breaking down winning scores per category.

Joining forces 🤝: Subqueries with joins

When counting related rows from multiple tables, an INNER JOIN combined with a subquery aggregation could be your go-to:

SELECT 
  p.product_name, 
  c.category_name, 
  sc.subcategory_count
FROM 
  products p
INNER JOIN categories c ON p.category_id = c.category_id /*(Matchmaking time😉)*/
INNER JOIN (
  SELECT 
    category_id, 
    COUNT(*) as subcategory_count
  FROM 
    subcategories
  GROUP BY 
    category_id /*(Who belongs with who?)*/
) sc ON c.category_id = sc.category_id;

Just like assigning chores to housemates.

War of the tools: Subquery vs window function

Choose your weapon wisely. Go for subqueries to isolate counting logic, use window functions for a clear syntax and performance perks when counting over partitions of your data.

Group By: A detective’s best friend

GROUP BY lets you aggregate data based on specific columns. It's like a detective looking for patterns. Very handy when detailed categorization is necessary.

explain-codes / Sql / Sql subquery with COUNT help

Linked

How can I get multiple counts with one SQL query?



Best way to get result count before LIMIT was applied



View's SELECT contains a subquery in the FROM clause



How to fetch the row count for all tables in a SQL SERVER database



Is it possible to use the SELECT INTO clause with UNION [ALL]?

