Understanding the Difference Between sum(1) and sum(count(*)) in SQL

Explore the differences between `sum(1)` and `sum(count(*))` in SQL, and learn how these commands impact your query results with practical examples.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Difference between sum(1) and sum(count*)) in sql?

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Decoding SQL: Understanding sum(1) vs. sum(count(*))

SQL queries can sometimes be perplexing, especially when different expressions yield varying results. A common point of confusion arises from the difference between SUM(1) and SUM(COUNT(*)). In this post, we'll explore these two SQL functions, how they behave, and what that means for your data queries.

The Setup: Your Table and Queries

Before diving into the explanation, let’s set the stage with a brief overview of the table we are working with. Here’s a simplified structure of our games table:

[[See Video to Reveal this Text or Code Snippet]]

We have a simple dataset recording the games played, and now we'll review two SQL queries that operate on this table and return different results:

Query with SUM(1)

Query with SUM(COUNT(*))

Query Results Overview

[[See Video to Reveal this Text or Code Snippet]]

This query produces the following results:

[[See Video to Reveal this Text or Code Snippet]]

[[See Video to Reveal this Text or Code Snippet]]

This query produces the following results:

[[See Video to Reveal this Text or Code Snippet]]

Explaining the Results: The Main Differences

SUM(1): A Count of Rows

Function: SUM(1) essentially counts the number of rows in the result set generated by each partition (in this case, by date).

Behavior: When this function is used, it counts one for each row that appears.

In our context:

For the date 2023-01-05, there are two entries for the item_id 'syd', producing a total of 1 unique entry for that date in the column total_matches.

SUM(COUNT(*)): Cumulative Counts of Items

Function: In contrast, SUM(COUNT(*)) evaluates the count of the items in each group before summing those counts.

Behavior: This approach gives a collective view of how many games are played for a certain date.

To illustrate:

For the date 2023-01-05, COUNT(*) returns 2 since there are two rows with 'syd'. Thus, SUM(COUNT(*)) will simply reflect that count in the column item_game_ratio.

Detailed Breakdown of the Differences:

Date 2023-01-05:

For SUM(1) → 1 total unique item on that date.

For SUM(COUNT(*)) → 2 total games played on that date.

Date 2023-01-10:

For SUM(1) → 1 unique item counted.

For SUM(COUNT(*)) → 2 games played on that date.

Conclusion: Understanding Your SQL Expressions

The difference between SUM(1) and SUM(COUNT(*)) is a powerful lesson in understanding how SQL aggregates data. While SUM(1) counts the existence of rows, SUM(COUNT(*)) goes a step further to sum the counts based on your groupings.

Being aware of these differences will enhance your SQL querying skills, enabling you to fetch precisely the data you need without confusion. Take a moment to review your queries and choose the function that aligns with your dataset’s requirements!