How can I create summaries across columns?

I have an existing application database table supporting an application, and I’ve been tasked with adding another feature. Currently, we have a build table that stores information about product builds. This table will not be changed, but it contains a primary key, BuildId, that will be used to cross reference the new table.

The new table contains a list of errors and warnings that occurred during the build. It will contain a reference to the primary key from the other table, which will also be the primary key in this table. Then, the user can select the errors and warnings that applied to the build.

Can I create summaries across the columns? For example, if I need to get the total number of errors on a build, I have to include each of the columns in the query. This can get pretty bloated since there are currently 12 and 10 errors and warnings, respectively.

I’m adding a sql fiddle that shows a trimmed down version of my current table, and one of the queries I’m trying to build.

I plan to run a number of different queries against the dataset. Counts of warnings/errors, queries to find specific errors (such as Error1 = true), etc. An example query (which is also seen in the fiddle link below) is:

CREATE TABLE SystemProblems (
  BuildId int primary key not null,
  Error1 bit,
  Error2 bit,
  Error3 bit,
  Error4 bit,
  Warning1 bit,
  Warning2 bit,
  Warning3 bit,
  Warning4 bit
)

insert into SystemProblems values
(1, 1, 0, 0, 0, 1, 1, 0, 1),
(2, 0, 1, 0, 1, 0, 0, 1, 0),
(3, 0, 0, 0, 0, 0, 0, 0, 0)

select 
  BuildId,
  CASE WHEN Error1 = 1 Then 1 Else 0 END +
    CASE WHEN Error2 = 1 THEN 1 Else 0 END +
    CASE WHEN Error3 = 1 THEN 1 Else 0 END +
    CASE WHEN Error4 = 1 THEN 1 Else 0 END 
  as ErrorCount,
  CASE WHEN Warning1 = 1 Then 1 Else 0 END +
    CASE WHEN Warning2 = 1 THEN 1 Else 0 END +
    CASE WHEN Warning3 = 1 THEN 1 Else 0 END +
    CASE WHEN Warning4 = 1 THEN 1 Else 0 END 
  as WarningCount
from SystemProblems
Where BuildId = 1

This returns the correct analysis, but as you can imagine, it will get messy with all of the columns. I’ve considered creating a function to clean up the query, but I’m not sure if I can pass a whole row to a function without specify each column individually.

I’d appreciate any help here. Can this reasonably done using a function, stored procedure, or sql syntax with which I’m unfamiliar? Is my design flawed?

Thanks!

Editing to mention that each of the columns maps to a predetermined error. For example, Error1 might map to “Part doesn’t fit correctly”. So, we shouldn’t run into a case where there are more errors than available fields. At that time, we’d just add a new column.

http://sqlfiddle.com/#!18/cba91/9

Answer

You can just use CROSS APPLY and conditional aggregation as the following:

SELECT SP.BuildId,
       SUM(CASE WHEN Errors = 1 THEN 1 ELSE 0 END) ErrorsSum,
       SUM(CASE WHEN Warnings = 1 THEN 1 ELSE 0 END) WarningsSum
FROM SystemProblems SP
CROSS APPLY
(
  VALUES
  (Error1, Warning1),
  (Error2, Warning2),
  (Error3, Warning3),
  (Error4, Warning4)
) T(Errors, Warnings)
GROUP BY SP.BuildId;

Which will returns:

+---------+-----------+-------------+
| BuildId | ErrorsSum | WarningsSum |
+---------+-----------+-------------+
|       1 |         1 |           3 |
|       2 |         2 |           1 |
|       3 |         0 |           0 |
+---------+-----------+-------------+

Here is a db<>fiddle

Advertisement

Answer