locked
Group by range RRS feed

  • 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

Answers

  • 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 
    	BirthDates.BirthDate <= DATEADD(Year, -50, '01/01/2011')
    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 Naomi N Sunday, October 2, 2011 7:21 AM
    • Marked as answer by KJian_ 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 
    	BirthDates.BirthDate <= DATEADD(Year, -50, '01/01/2011')
    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 Naomi N Tuesday, October 4, 2011 4:14 AM
    • Edited by Brent Serbus Tuesday, October 4, 2011 4:01 PM edit
    • Proposed as answer by Shaw6 Wednesday, October 5, 2011 5:28 AM
    • Marked as answer by KJian_ 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 
    	BirthDates.BirthDate <= DATEADD(Year, -50, '01/01/2011')
    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 Naomi N Sunday, October 2, 2011 7:21 AM
    • Marked as answer by KJian_ 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 
    	BirthDates.BirthDate <= DATEADD(Year, -50, '01/01/2011')
    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 Naomi N Tuesday, October 4, 2011 4:14 AM
    • Edited by Brent Serbus Tuesday, October 4, 2011 4:01 PM edit
    • Proposed as answer by Shaw6 Wednesday, October 5, 2011 5:28 AM
    • Marked as answer by KJian_ 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
    Answerer