SQL Group by a range of value using T-SQL

Target audience: SQL programmers
Used tools: T-SQL
What’s the purpose: group records from SQL data table using aggregate functions with derived tables

Now, let’s try to make the ‘job’ with t-sql only. The key point here is to use aggregate functions with so called derived tables.In this article http://logicalread.solarwinds.com/when-to-apply-sql-server-derived-table we can find a definition and sample for derived table: “A derived table is a table expression that appears in the FROM clause of a query. You can apply derived tables when the use of column aliases is not possible because another clause is processed by the SQL translator before the alias name is known.” And here is an example:

USE sample;
SELECT MONTH(enter_data) AS enter_month
FROM works_on
GROUP BY enter_month;

The result will be error message : The invalid column ‘enter_month’ because clause GROUP BY will be process before SELECT command and enter_month is not known when grouping is fulfillment. With derived table we can resolve this problem:

USE sample;
SELECT enter_month
FROM (SELECT MONTH(enter_data) AS enter_month
              FROM works_on) AS m
GROUP BY enter_month;

Derived tables are not the same as a temporary or physical tables where the fields and data types are declared. They are not subqueries too. Instead, as the name implies, the table is derived from an existing table. They are not views because a view is an object that is created and is re-usable. A derived table is only available at execution time and not a re-usable object within the query. In other words, you cannot reference it multiple times like you can with common table expressions (CTE’s)

As Microsoft documentation, CTE (common table expression) is: “A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.”

A CTE can be used to:
– Create a recursive query.
– Substitute for a view when the general use of a view is not required that is, you do not have to store the definition in metadata.
– Enable grouping by a column that is derived from a scalar subselect or a function that is either not deterministic or has external access.
– Reference the resulting table multiple times in the same statement.
Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated. CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.After this “piece of theory”, here is the solution of our problem.
We have the same table:

with such data :

dbTPBPsbAmmount
20001.11
20001.32
10003.21
97002.00
97000.10
10000.10
10000.10
10000.10
10000.10
20000.10
90002.00
10002.00
10003.21
10003.21
97001.32
97001.32
97002.18
97001.23
97001.00
10002.00
97002.00

Derived tables become really useful when you want to start using aggregated statements. In our example, the query aggregates count and total sums of orders by municipality and country regions. The t-sql code is:

SELECT ISNULL(dTable.[NAME RU],'TOTAL') AS [NAME RU], 
       ISNULL(dTable.[dbTPBPname],'SUM') AS [OPS],
       COUNT(dTable.[dbCode]) AS [COUNT],
       SUM(dTable.[dbAmount]) AS [TOTAL SUM]
FROM
(
    SELECT CASE
     WHEN dbTPBP BETWEEN 1000 AND 3999 THEN 'Western'
     WHEN dbTPBP BETWEEN 4000 AND 4999 THEN 'South-Central'
     WHEN dbTPBP BETWEEN 5000 AND 5999 THEN 'North-Central'
     WHEN dbTPBP BETWEEN 6000 AND 6299 THEN 'South-Eastern'
     WHEN dbTPBP BETWEEN 6300 AND 6999 THEN 'South-Central'
     WHEN dbTPBP BETWEEN 7000 AND 7699 THEN 'North-Central'
     WHEN dbTPBP BETWEEN 7700 AND 7999 THEN 'North-Eastern'
     WHEN dbTPBP BETWEEN 8000 AND 8999 THEN 'South-Eastern'
     WHEN dbTPBP BETWEEN 9000 AND 9999 THEN 'North-Eastern'
     WHEN dbTPBP >=      9999          THEN ''
    END AS [NAME RU],dbTPBPname,dbCode,dbAmount
    FROM bpPayment.dbo.grabPmnt
) AS dTable
  GROUP BY dTable.[NAME RU],dtable.[dbTPBPname] WITH ROLLUP

The result is:

NAME RUOPSCOUNTTOTAL SUM
North-EasternVARNA12.00
North-EasternDOBRICH811.15
North-EasternSUM913.15
WesternSOFIA1317.67
Western SUM 1317.67
TOTAL SUM 2230.82

Here is the time to read about the difference in using GROUPING – an aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP and ISNULL(expression, alternative_value) function which lets you return an alternative value when an expression is NULL – as example from http://mangalpardeshi.blogspot.bg/2009/05/grouping-function-sql-server.html

Published By
Categorized as SQL