Computed Columns for Grouping–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I ran into someone trying to do some grouping for an accounting report. In this case, they had a number of criteria where certain accounts were used to produce groups of values. For example, I might have these criteria:

  • Accounts from 400000 to 490000 are Operating Expenses
  • Accounts from 500000 to 502999 are Personal Expenses
  • Accounts from 503000 to 503999 are Materials and Services

There would be other items, but if I have accounts and values, how do I sum and group in these ways?

There are a few possibilities, but since I might move accounts around, I thought that computed columns might help with grouping here. This post looks are a way you can do this.

Imagine I have some values like this:

CREATE TABLE BudgettoActual
(accountid INT
, budget NUMERIC(10,2)
, actual NUMERIC(10,2)
)
GO
INSERT dbo.BudgettoActual
     (accountid, budget, actual)
VALUES
     (400010, 300, 299),
     (501010, 100, 102),
     (502010, 200, 150),
     (503010, 400, 150),
     (507010, 800, 150)
GO

Now I can use a SUM with a CASE, but that makes a complex query. One way to simplify this for others is to use a computed column in the table that might include my criteria. I can use a CASE statement to create my groupings.

ALTER TABLE dbo.BudgettoActual 
ADD AccountGroup AS CASE
    WHEN accountid>= 400000 AND accountid < 489999 THEN 1
    WHEN accountid>= 501000 AND accountid < 503000 THEN 2
    WHEN accountid>= 503000 AND accountid < 504000 THEN 3
    WHEN accountid>= 507000 AND accountid < 508000 THEN 4
  ELSE 5
  END

With this, I see this in my table:

2021-05-12 11_35_15-SQLQuery5.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (52))_ - Microsoft SQL Server

Now I have these different groups that I can use in a query and group by them. For example, I can get a quick look at my different categories with this code. I’ve put the categories in a CASE in the column list, but it could come from another table.

SELECT
          CASE
              WHEN ba.AccountGroup = 1 THEN
                  'Operating Revenues'
              WHEN ba.AccountGroup = 2 THEN
                  'Personal Expenses'
              WHEN ba.AccountGroup = 3 THEN
                  'Materials and Services'
              WHEN ba.AccountGroup = 4 THEN
                  'Reserves'
          END 'Object'
        , SUM (ba.budget) AS Budget
        , SUM (ba.actual) AS actual
FROM     dbo.BudgettoActual AS ba
GROUP BY ba.AccountGroup;

This gives me a look at my financial numbers quickly.

2021-05-12 11_36_20-SQLQuery5.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (52))_ - Microsoft SQL Server

Rather than numbers, I could have used the title in the computed column, but that causes issues with ordering. With these numbers, I can choose numbers that are the order I need them in for a report (which can matter for financial reporting).

I’d prefer to use a separate table mapping the AccountGroup to a title and then joining that in my report.

This isn’t the only way to do this, but it is one way to handle complex grouping in a way that can make it easier for clients that might need to query this data.

SQLNewBlogger

This post took me about 10 minutes to write, but about 15 minutes to setup, which might be most of a writing session for a blog. However, it’s a good showcase of a creative way to solve an issue.

Any of you could put together a similar post on a query issue you’ve run into and want to share.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.