Marko Vujanic

    Built with ConvertKit

    SQL Count

    Discovering the Hidden Power of COUNT in SQL #

    As someone who's been exploring the vast world of SQL, I recently stumbled upon a little gem that transformed how I write queries. Up until now, I’ve been on a mission to use CTEs (Common Table Expressions) every chance I get. I just learned about them and, honestly, I want to find excuses to “abuse” them, even when they aren’t strictly necessary.

    But here's the thing—sometimes less is more, and that’s a lesson I learned the hard way with the COUNT function.

    The Journey Begins #

    I had this task: calculate the total number of customers in a database and figure out what percentage of them had an email address. Being the CTE enthusiast that I am, my initial approach looked something like this:

    WITH email_stats AS (
        SELECT 
            COUNT(*) AS total_customers,
            SUM(CASE WHEN email IS NOT NULL THEN 1 ELSE 0 END) AS customers_with_email
        FROM customer
    )
    SELECT 
        total_customers AS "Total Customers",
        customers_with_email AS "Customers with Email",
        (customers_with_email * 100.0 / total_customers) AS "Percentage with Email"
    FROM email_stats;

    This query worked fine. It used a CTE, which made me happy, and it got the job done. But then I had a revelation about the COUNT function that made me rethink everything.

    The Secret Power of COUNT #

    Here’s what I discovered: COUNT isn’t just a blunt instrument for counting all rows. It’s smarter than that. It can count specific expressions directly, which means you can simplify your queries and make them more efficient.

    Instead of my initial approach, I found I could do this:

    SELECT 
        COUNT(*) AS Total, 
        COUNT(email) AS WITH_EMAIL,
        COUNT(email) * 100.0 / COUNT(*) AS "Percentage with Email"
    FROM customer;

    And by 'I found it', it actually means I learned the solution from an excellent course I'm taking at https://www.masterywithsql.com/.

    The Realization #

    A Practical Example #

    Let’s say my customer table has the following data:

    customer_id email
    1 alice@example.com
    2 bob@example.com
    3 NULL
    4 charlie@example.com
    5 NULL

    When I run my simplified query:

    SELECT 
        COUNT(*) AS Total, 
        COUNT(email) AS WITH_EMAIL,
        COUNT(email) * 100.0 / COUNT(*) AS "Percentage with Email"
    FROM customer;

    I get results like this:

    Total WITH_EMAIL Percentage with Email
    5 3 60.0

    The Takeaway #

    So, what’s the big lesson here? Sometimes, simplicity trumps everything else. Even though I’m tempted to throw a CTE at every problem, there are moments when the power of SQL functions like COUNT can streamline your query in ways I hadn’t considered before.

    This discovery might be old news to SQL pros, but for me, it was a game-changer. It’s a reminder that while it’s fun to explore and experiment with new tools, the real magic often lies in understanding the basics more deeply.

    But don’t worry—I’m still going to find ways to sneak in those CTEs whenever I can!

    Anki Cards #