Using SQL CASE and GROUP BY in the Same SELECT Statement

According to Wikipedia SQL has been around for a loooong time, since 1970 to be precise and it shows in the syntax. However, due to the fact that it is the most effective way to retrieve information from a relational database you cannot get around knowing the ins-and-outs of this complicated language.

Fortunately though, you can get a lot of help out there on the Internet when it comes to building complicated queries. Without sites like Stackoverflow and W3Schools I would be hopelessly lost even though I have been building SQL queries for years now. There is always something new to learn.

RTagAnd just recently I was faced with a new challenge: How to use the SQL CASE statement and GROUP BY function in one query. The requirement was to create a report that aggregated sales and cost of goods sold in an Epicor ERP System with a Microsoft SQL Server database back end. I am using the powerful reporting tool R-Tag to build Pivot Tables and Pivot Graphs to generate monthly trend reports for a Client’s sales team.

Google research lead me to some good articles that lead me on the right track:

Breakdown of this SELECT statement (note that the syntax may be specific to R-Tag and will need adjusting for other development environments such as Crystal):

I want to show sales (“extended_price”) and cost of goods sold (“cogs_amount”) for the selected period (“PeriodFromDate” to “PeriodToDate”) and sales engineer (“SalesEngineer”) aggregated into two categories:

  • SalesCategory1 if “product_group_id” is equal to  ‘GROUP1’, ‘GROUP2’, ‘GROUPX’ or ‘MISC’
  • SalesCategory2 otherwise

There are three parameters to determine the start and end dates for the report and the assigned sales engineer:

  • {@PeriodFromDate}
  • {@PeriodToDate}
  •  {@SalesEngineer}