A SUM() and COUNT() ploy

Photo by Vitolda Klein on Unsplash

When we speak about aggregating our data the two of the most important functions that cross our mind are SUM() and COUNT(). SUM() is used to sum all the values in the column and COUNT() is used to count the values in the column or count the number of rows in the table. Both of these functions serve their purpose very well but had you ever thought that we can use SUM() to count?

Yes, you read it right. In this really short and quick-read article, I am going to talk about a technique by which we can use SUM() to behave like a COUNT() function, which will help us get the required data at the column level which would have otherwise been achieved by using a JOIN or a PIVOT.

Let’s use the below data:

%sql
select * from student_marks

Student Marks Data

The task here is to count and group students into various groups depending on the marks they have scored. We have to group students with marks 0 to 59, marks between 60 to 89, and 90 to 100 in every subject together.

One of the obvious ways that come to our mind is writing the below query:

%SQL
SELECT    t0.subject,
          CASE
                    WHEN marks_90_to_100 IS NULL THEN 0
                    ELSE marks_90_to_100
          end AS marks_90_to_100,
          CASE
                    WHEN marks_60_to_89 IS NULL THEN 0
                    ELSE marks_60_to_89
          end AS marks_60_to_89,
          CASE
                    WHEN marks_00_to_59 IS NULL THEN 0
                    ELSE marks_00_to_59
          end AS marks_00_to_59
FROM      (
                   SELECT   subject,
                            COUNT(*) AS total
                   FROM     student_marks
                   GROUP BY subject ) t0
LEFT JOIN
          (
                   SELECT   subject,
                            COUNT(*) AS marks_90_to_100
                   FROM     student_marks
                   WHERE    marks >= 90
                   GROUP BY subject ) t1
ON        t0.subject = t1.subject
LEFT JOIN
          (
                   SELECT   subject,
                            COUNT(*) AS marks_60_to_89
                   FROM     student_marks
                   WHERE    marks >= 60
                   AND      marks < 90
                   GROUP BY subject ) t2
ON        t0.subject = t2.subject
LEFT JOIN
          (
                   SELECT   subject,
                            COUNT(*) AS marks_00_to_59
                   FROM     student_marks
                   WHERE    marks < 60
                   GROUP BY subject ) t3
ON        t0.subject = t3.subject

If we evaluate this query, we add multiple filters on the `marks` column, group my data on a `subject` column and COUNT() students, and end up joining them to get the required data at the column level. The above query involves too many steps and hence we end up writing a bigger query to get the below output.

Output Using COUNT()

We can do the exact same thing by using SUM() with the case expression. This will give you the output just the way COUNT() would have, in addition to that you will have the data at column level without joining it.

Yes!!! All we need to know is if we use a case statement to return a value of 0 or 1 for the required table rows. It will help us to count the values.

Let’s see how:

%sql
SELECT subject,
       SUM(CASE
             WHEN marks >= 90 THEN 1
             ELSE 0
           end) AS marks_90_to_100,
       SUM(CASE
             WHEN marks >= 60
                  AND marks < 90 THEN 1
             ELSE 0
           end) AS marks_60_to_89,
       SUM(CASE
             WHEN marks < 60 THEN 1
             ELSE 0
           end) AS marks_00_to_59
FROM   student_marks
GROUP  BY subject

Output Using SUM()

Surprised? Let us evaluate this query and see how it has done the trick for us.

In the case statement, every time a condition evaluates to true it assigns value 1 to the row, and else 0. Since we have wrapped this case statement inside the SUM() it does its job of summing up all the 1’s and 0s which is equivalent to counting the number of rows.

Summary

In this article, we have seen how using SUM() to count the number of values gives us a well-defined output. So, next time when you think of such scenarios, remember to make the most out of this technique!