MYSQL Select Query with SUM()

MYSQL Select Query with SUM()

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Aggregating Metrics by Group in MySQL: A SUM() Pattern for Campaign Data

When you need to aggregate numeric columns from a single table while grouping results by another column, the core mechanism is simple: use GROUP BY. However, the specific challenge presented here involves summing boolean-like flags (clicked and viewed) to derive engagement metrics per source. The confusion often stems from not understanding how SUM() handles non-zero integers in SQL versus how it treats zero values.

In your dataset, the columns clicked and viewed act as binary indicators. A value of 1 signifies an event occurred, while 0 signifies it did not. You do not need complex loops or procedural logic to iterate through unique source_ids. Instead, you leverage the aggregation capabilities of MySQL directly in a single SELECT statement.

The Aggregation Query Structure

To get the output showing total rows, clicked counts, and viewed counts for each source_id, you must group your results by that identifier. The query structure looks like this:

SELECT 
    source_id,
    SUM(clicked) AS total_clicked,
    SUM(viewed) AS total_viewed
FROM campaign_stats
GROUP BY source_id;

However, looking at the desired output format (xxx > Total: 3 // Clicked: 2 // Viewed 1), there is a specific requirement to include the Total row count. In SQL, COUNT(*) returns the number of rows in the group, whereas SUM(column) returns the sum of values in that column. Since your data uses 0 and 1, SUM(clicked) effectively counts how many times a click happened, but it will not tell you the total number of records processed for that source unless you explicitly count them.

Implementing the "Total" Metric

You cannot derive the total row count from summing the binary columns alone. If a source has 3 rows where clicked is 0 and 2 are 1, SUM(clicked) gives you 2. But if there was a third row with clicked=0, SUM still only sees zeros. To get the "Total" figure shown in your example (e.g., Total: 3 for xxx), you must include a COUNT(*).

Here is the refined query that matches your exact output requirements:

SELECT 
    source_id,
    COUNT(*) AS total,
    SUM(clicked) AS clicked,
    SUM(viewed) AS viewed
FROM campaign_stats
GROUP BY source_id;

This query produces the following result set based on your sample data:

source_id total clicked viewed
xxx 3 2 1
yyy 4 2 1

Formatting the Output String

Your desired output format (xxx > Total: 3 // Clicked: 2 // Viewed 1) implies a string concatenation rather than a standard tabular result. This is often required when feeding data into logging systems, dashboards, or custom reports that expect a specific text format.

In MySQL, you can achieve this using the CONCAT() function combined with conditional logic if needed. If you strictly need the output to appear as a single string per row, modify the SELECT list:

SELECT 
    source_id,
    CONCAT(
        source_id, 
        ' > Total:', COUNT(*), 
        ' // Clicked:', SUM(clicked), 
        ' // Viewed:', SUM(viewed)
    ) AS formatted_stats
FROM campaign_stats
GROUP BY source_id;

Performance and Indexing Considerations

When scaling this pattern from a few rows to millions, the GROUP BY clause becomes critical for performance. MySQL will scan the entire table to calculate these aggregates unless hints are provided or indexes exist.

Ideally, you should have an index on the source_id column. Since source_id is the grouping key, the database can use this index to quickly locate partitions of data and apply the aggregation functions without a full table scan. If your table lacks an index on source_id, the query planner will be forced to perform a full table read for every execution, which degrades significantly as row counts increase.

Common Pitfalls to Avoid

A frequent mistake developers make is assuming that SUM() can count rows regardless of the column values. Remember that SUM(column) adds up the numeric values; if your data contained nulls or if you needed an accurate headcount including rows where clicked was null, COUNT(*) remains the robust choice for the total metric.

Additionally, ensure that the source_id column is of a consistent type. Mixing string and integer types in the same column can cause unexpected grouping behavior, especially if MySQL performs implicit type conversions during the join or group operation. Stick to one data type definition for your unique identifiers.

If you are building this application using Laravel, you might consider leveraging Eloquent's aggregation methods, but for raw performance or specific formatting needs like string concatenation, executing a raw query with DB::select() often provides more control over the final output format.

Our network