Conditional count on a field

sql

prompt-engineering

best-practices

join

byNikita Barsukov·Aug 31, 2024

Perform a conditional count in SQL using COUNT along with CASE:

SELECT COUNT(CASE WHEN condition THEN 1 END) AS condition_count FROM table;

Replace condition with your explicit criteria and table with the name of your table. This usage allows you to count rows that fulfill the condition, resulting in condition_count. It’s a swift and tactical approach for enumerating instances in a dataset.

Key to efficient queries: structuring for performance

When varied conditions are at play, or numerous counters exist in a single query, it's pivotal to streamline the logic avoiding sluggish queries. Combining SUM and CASE WHEN...END in one command achieves this:

--Query structure for optimized performance
SELECT
  jobID,               -- Group results by jobID and JobName for neat reports
  JobName,
  SUM(CASE WHEN                    -- Avoid lag! Process all conditions in one go
      Priority = 'High' THEN 1 
    ELSE 0 END) AS HighPriorityCount, 
  SUM(CASE WHEN 
      Priority = 'Medium' THEN 1 
    ELSE 0 END) AS MediumPriorityCount, 
  SUM(CASE WHEN 
      Priority = 'Low' THEN 1 
    ELSE 0 END) AS LowPriorityCount
FROM
  Jobs
GROUP BY
  jobID,
  JobName;

Consider the Jobs table with fields jobID, JobName, and Priority. Grouping by jobID and JobName presents counts by job in a more compact and organized format.

Alternate syntax in different SQL flavors

SQL, like a world cuisine, offers an interesting range of alternative syntax across databases. Case in point, the SQL Server's IIF:

SELECT jobID, JobName, 
COUNT(IIF(Priority = 'High', 1, NULL)) AS HighPriorityCount
FROM Jobs 
-- Easy peasy! It's like the fortune cookie of SQL Server - short & sweet!
GROUP BY jobID, JobName;

It's a compact version of CASE and useful for simplifying queries. However, the CASE expression in ANSI SQL-92 is a better choice for cross-database compatibility.

Readability is king, long live the query!

Proper formatting ensures your SQL looks like a well-tailored suit - sharp and easy on the eyes. Break your lines where it makes sense, use indentation to show the query's structure, and don’t shy away from comments:

-- Query: the invisible Swiss knife of your database
SELECT 
  JobName,
  SUM(CASE WHEN Priority = 'High' THEN 1 ELSE 0 END) AS HighCount, -- High stakes game
  SUM(CASE WHEN Priority = 'Medium' THEN 1 ELSE 0 END) AS MediumCount,-- Medium, rare?
  -- Omit ELSE to keep it hip (NULLs are ignored by SUM anyway!)
  SUM(CASE WHEN Priority = 'Low' THEN 1 END) AS LowCount
FROM 
  Jobs
GROUP BY 
  JobName;

Embrace your missing values

LEFT JOIN paired with COALESCE comes to the rescue when dealing with null counts. It retrieves all priority values, converting null counts to zero:

-- Task force ASSEMBLE! Left join and COALESCE are the Avengers of SQL
SELECT 
  j.JobName, 
  COALESCE(jp.HighCount, 0) AS HighCount, -- Null-to-zero shield
  COALESCE(jp.MediumCount, 0) AS MediumCount, -- Null-to-zero cloak
  COALESCE(jp.LowCount, 0) AS LowCount -- Null-to-zero lance
FROM 
  (SELECT DISTINCT Priority FROM Jobs) AS Priorities -- Our infinity stone collection
LEFT JOIN  
  (SELECT 
    JobName, 
    SUM(CASE WHEN Priority = 'High' THEN 1 ELSE 0 END) AS HighCount,
    SUM(CASE WHEN Priority = 'Medium' THEN 1 ELSE 0 END) AS MediumCount,
    SUM(CASE WHEN Priority = 'Low' THEN 1 END) AS LowCount
   FROM 
     Jobs
   GROUP BY 
     JobName
  ) AS jp ON jp.Priority = Priorities.Priority;

Flexing complexity with subqueries

To tame more intricate conditions and permit segregation, subqueries emerge as your best bet:

-- The Matryoshka doll of SQL. A query within a query.
SELECT 
  j.JobName,
  jp.HighCount,
  jp.MediumCount,
  jp.LowCount
FROM 
  Jobs j
JOIN 
  (SELECT 
    jobID, 
    SUM(CASE WHEN Priority = 'High' THEN 1 ELSE 0 END) AS HighCount,
    SUM(CASE WHEN Priority = 'Medium' THEN 1 ELSE 0 END) AS MediumCount,
    SUM(CASE WHEN Priority = 'Low' THEN 1 ELSE 0 END) AS LowCount
   FROM 
     Jobs
   GROUP BY 
     jobID
  ) AS jp ON jp.jobID = j.jobID;

Stand united with UNION ALL

UNION ALL has the power to combine results. Let’s say, you want to catalog all priorities, even if they're devoid of any rows:

SELECT 'High' as Priority, COUNT(*) as Count FROM Jobs WHERE Priority = 'High'
UNION ALL
SELECT 'Medium', COUNT(*) FROM Jobs WHERE Priority = 'Medium'
UNION ALL
SELECT 'Low', COUNT(*) FROM Jobs WHERE Priority = 'Low';