# Group by range

• ### Question

• I need to create a report that shows the count of clients by income group who were at least 50 years old on 1/1/2011. The income groups are: group 1 (Income less than \$50,000), group 2 (Income equal to or greater than \$50,000 and less than \$100,000), group 3 (Income equal to or greater than \$100,000 and less than \$150,000), and group 4 (Income equal to or greater than \$150,000).
There are two tables: Client_Income and Client_Birthdate
Client_Income has two fields Client_Social_Security_No and Income
Client_Birthdate has two fields Client_Social Security_No and Birthdate (format yyyymmdd)

thanks a lot.

Friday, September 30, 2011 6:57 PM

• ```Declare @ClientIncome Table(SSN Varchar(11), Income Money)
Declare @ClientBirthDate Table (SSN Varchar(11), BirthDate Date)

Insert @ClientIncome
Values ('11', 49999), ('12', 40000), ('13', 100000), ('14', 170000)

Insert @ClientBirthDate
Values ('11', '01/01/1950'), ('12', '01/01/1952'), ('13', '01/01/1963'), ('14', '01/01/1940')

Select
(Case
When Incomes.Income < 50000 Then 'Grp1'
When Incomes.Income >= 50000 And Incomes.Income < 100000 Then 'Grp2'
When Incomes.Income >= 100000 And Incomes.Income < 150000 Then 'Grp3'
When Incomes.Income >= 150000 Then 'Grp4'
End)
,COUNT(*)
From @ClientIncome As Incomes
Inner Join @ClientBirthDate As BirthDates On Incomes.SSN = BirthDates.SSN
Where
Group By
(Case
When Incomes.Income < 50000 Then 'Grp1'
When Incomes.Income >= 50000 And Incomes.Income < 100000 Then 'Grp2'
When Incomes.Income >= 100000 And Incomes.Income < 150000 Then 'Grp3'
When Incomes.Income >= 150000 Then 'Grp4'
End)

--output
---- -----------
Grp1 2
Grp4 1
```

Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
• Proposed as answer by Sunday, October 2, 2011 7:21 AM
• Marked as answer by Friday, October 7, 2011 7:57 AM
Friday, September 30, 2011 7:16 PM
• The first part of Arbi's reply is only for demonstration. You only need the actual query and you need to use your own table, so

```SELECT
(Case
When Incomes.Income < 50000 Then 'Grp1'
When Incomes.Income >= 50000 And Incomes.Income < 100000 Then 'Grp2'
When Incomes.Income >= 100000 And Incomes.Income < 150000 Then 'Grp3'
When Incomes.Income >= 150000 Then 'Grp4'
End)
,COUNT(*)
From dbo.Incomes -- here we need your table
Inner Join dbo.BirthDates -- again, your table

On Incomes.SSN = BirthDates.SSN
Where
Group By
(Case
When Incomes.Income < 50000 Then 'Grp1'
When Incomes.Income >= 50000 And Incomes.Income < 100000 Then 'Grp2'
When Incomes.Income >= 100000 And Incomes.Income < 150000 Then 'Grp3'
When Incomes.Income >= 150000 Then 'Grp4'
End)
```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Edited by Tuesday, October 4, 2011 4:14 AM
• Edited by Tuesday, October 4, 2011 4:01 PM edit
• Proposed as answer by Wednesday, October 5, 2011 5:28 AM
• Marked as answer by Friday, October 7, 2011 7:57 AM
Tuesday, October 4, 2011 4:14 AM

### All replies

• ```Declare @ClientIncome Table(SSN Varchar(11), Income Money)
Declare @ClientBirthDate Table (SSN Varchar(11), BirthDate Date)

Insert @ClientIncome
Values ('11', 49999), ('12', 40000), ('13', 100000), ('14', 170000)

Insert @ClientBirthDate
Values ('11', '01/01/1950'), ('12', '01/01/1952'), ('13', '01/01/1963'), ('14', '01/01/1940')

Select
(Case
When Incomes.Income < 50000 Then 'Grp1'
When Incomes.Income >= 50000 And Incomes.Income < 100000 Then 'Grp2'
When Incomes.Income >= 100000 And Incomes.Income < 150000 Then 'Grp3'
When Incomes.Income >= 150000 Then 'Grp4'
End)
,COUNT(*)
From @ClientIncome As Incomes
Inner Join @ClientBirthDate As BirthDates On Incomes.SSN = BirthDates.SSN
Where
Group By
(Case
When Incomes.Income < 50000 Then 'Grp1'
When Incomes.Income >= 50000 And Incomes.Income < 100000 Then 'Grp2'
When Incomes.Income >= 100000 And Incomes.Income < 150000 Then 'Grp3'
When Incomes.Income >= 150000 Then 'Grp4'
End)

--output
---- -----------
Grp1 2
Grp4 1
```

Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
• Proposed as answer by Sunday, October 2, 2011 7:21 AM
• Marked as answer by Friday, October 7, 2011 7:57 AM
Friday, September 30, 2011 7:16 PM
• "A problem well stated is a problem half solved." -- Charles F. Kettering
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html
Arbi has several serious errors in his posting. And your narrative is also full of fundamental design errors. Of course you will never use the proprietary MONEY data types (it has math errors and does not port), you know that SSN is
ssn CHAR(9) NOT NULL CHECK (ssn LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
You also know that a DATE has no format; that was COBOL in the 1950's and not SQL.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
Friday, September 30, 2011 8:35 PM
• Celko,

Could you please guide me and let me know what are my serious errors?

If it is for SSN, I included "-" in the count. [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9].

Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
Friday, September 30, 2011 8:55 PM
• Could this be done without having to insert the values into the tables?
Tuesday, October 4, 2011 4:11 AM
• The first part of Arbi's reply is only for demonstration. You only need the actual query and you need to use your own table, so

```SELECT
(Case
When Incomes.Income < 50000 Then 'Grp1'
When Incomes.Income >= 50000 And Incomes.Income < 100000 Then 'Grp2'
When Incomes.Income >= 100000 And Incomes.Income < 150000 Then 'Grp3'
When Incomes.Income >= 150000 Then 'Grp4'
End)
,COUNT(*)
From dbo.Incomes -- here we need your table
Inner Join dbo.BirthDates -- again, your table

On Incomes.SSN = BirthDates.SSN
Where
Group By
(Case
When Incomes.Income < 50000 Then 'Grp1'
When Incomes.Income >= 50000 And Incomes.Income < 100000 Then 'Grp2'
When Incomes.Income >= 100000 And Incomes.Income < 150000 Then 'Grp3'
When Incomes.Income >= 150000 Then 'Grp4'
End)
```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Edited by Tuesday, October 4, 2011 4:14 AM
• Edited by Tuesday, October 4, 2011 4:01 PM edit
• Proposed as answer by Wednesday, October 5, 2011 5:28 AM
• Marked as answer by Friday, October 7, 2011 7:57 AM
Tuesday, October 4, 2011 4:14 AM
• Hi Life !

You may us ethe below to get your desired output;

```CREATE TABLE #Client_Income (Client_Identification_Number INT, Income INT)
INSERT #Client_Income
SELECT 1,50000 UNION ALL
SELECT 2,75000 UNION ALL
SELECT 3,100000 UNION ALL
SELECT 4,150000

CREATE TABLE #Client_Birthdate(Client_Identification_Number INT, Birthdate DateTime)
INSERT #Client_Birthdate
SELECT 1,'19500323' UNION ALL
SELECT 2,'19600414' UNION ALL
SELECT 3,'19750123' UNION ALL
SELECT 4,'19550611'

SELECT X.GroupName
, ISNULL(SUM(CASE
WHEN  CI.Income < 50000 AND DATEDIFF(yy,CB.BirthDate,'2011-01-01') >= 50 THEN 1
WHEN  CI.Income >= 50000 AND CI.Income < 100000 AND DATEDIFF(yy,CB.BirthDate,'2011-01-01') >= 50 THEN 1
WHEN  CI.Income >= 100000 AND CI.Income < 150000 AND DATEDIFF(yy,CB.BirthDate,'2011-01-01') >= 50 THEN 1
WHEN  CI.Income >= 150000 AND DATEDIFF(yy,CB.BirthDate,'2011-01-01') >= 50 THEN 1
END),0)
AS NumberOfClient
FROM (SELECT 'Group 1' AS GroupName UNION ALL SELECT 'Group 2' AS GroupName UNION ALL SELECT 'Group 3' AS GroupName UNION ALL SELECT 'Group 4' AS GroupName) AS X
LEFT JOIN #Client_Income CI ON
CASE
WHEN  CI.Income < 50000 THEN 'Group 1'
WHEN  CI.Income >= 50000 AND CI.Income < 100000 THEN 'Group 2'
WHEN  CI.Income >= 100000 AND CI.Income < 150000 THEN 'Group 3'
WHEN  CI.Income >= 150000  THEN 'Group 4'
END = X.GroupName
LEFT JOIN #Client_Birthdate CB ON CB.Client_Identification_Number = CI.Client_Identification_Number
GROUP BY X.GroupName
--GroupName NumberOfClient
--Group 1 0
--Group 2 2
--Group 3 0
--Group 4 1

```

Please let me know if this doesn’t work for you. Hope I have answered you correctly.

Thanks, Hasham

Tuesday, October 4, 2011 10:07 AM