Conditional SQL count

Speedy Gonzalez: Performance Enhancements

When life gives you a long list of conditions, don't stress. PostgreSQL has got you covered with the mighty crosstab() function. It transforms rows into columns, allowing for an efficient count across multiple conditions:

CREATE EXTENSION if not exists tablefunc;

SELECT * FROM crosstab(
  /*
  Who needs a superhero when you have SQL. This beauty groups users by the status 
  and counts them. See, it's like an Avengers team-up, but for data!
  */
  'SELECT user_id, status, COUNT(*)
   FROM Users
   GROUP BY user_id, status
   ORDER BY user_id, status'
) AS ct(user_id INT, ActiveCount INT, InactiveCount INT, SuspendedCount INT);


Don't forget to invite tablefunc to the party—you'll need this extension to summon the cool crosstab(). Also, always have proper group by etiquette!

Advanced: Count FIRST, Ask Something Else Later

Advanced scenarios beg for more than just a simple count. Say you need to count user statuses dynamically—without knowing them. PostgreSQL comes to the rescue with some funky JSON functions:

SELECT json_object_agg(status, COUNT(*)) AS StatusCounts
FROM Users
GROUP BY status;


This will spit out a JSON object with statuses as keys and their counts as values. Didn't remember Grandma's knitting club members? NO PROBLEM, PostgreSQL has you covered.

Situational Awareness: Combating Complex Conditions

When dealing with complex conditions needing an aggregated count for each, being the sharp tool in the shed is important! Here's your secret weapon:

SELECT
    user_id,
    /*
    It's like a game of 'Where's Wally?' but for your database.
    Light up each status the moment it appears!
    */
    COUNT(*) FILTER (WHERE status = 'Active') AS active_count,
    COUNT(*) FILTER (WHERE status = 'Inactive') AS inactive_count,
    -- Add further conditions as needed, like rare Pokémon cards!
FROM Users
GROUP BY user_id;


The Highlander: COUNT vs SUM

Couldn't decide between COUNT and SUM? Fear no more!

SELECT
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS ActiveCount
FROM Users;


But remember... there can be only one... winner. FILTER clause with COUNT() outshines its SUM rival in performance and expressiveness.

Sketching for Success

Complex queries? Draw it, mate. Simple table, flowchart, a piece of modern art—you name it. Seeing the data groupings in the flesh can make SQL query construction as easy as pie.