SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Average age want to get
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

erdogan_ozkaya
Starting Member

8 Posts

Posted - 04/08/2013 :  05:48:47  Show Profile  Reply with Quote
Hi Friends,

Got a table named MEMBERS,

AREAS
name,
surname,
Age

Average age want to get

e.
18 - 25 age 30%
25 - 35 age 40%
35 - 45 age 20%
10% over the age of 45

thanks

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 04/08/2013 :  06:15:33  Show Profile  Reply with Quote
--May be this?

SELECT (COUNT(CASE WHEN Age >=18 AND Age<25 THEN Age END)*100)/COUNT(*) [18-25],
	   (COUNT(CASE WHEN Age >=25 AND Age<35 THEN Age END)*100)/COUNT(*) [25-35], 
	   (COUNT(CASE WHEN Age >=35 AND Age<45 THEN Age END)*100)/COUNT(*) [35-45],
	   (COUNT(CASE WHEN Age >=35 THEN Age END)*100)/COUNT(*) [>=45]
FROM @Members
Go to Top of Page

erdogan_ozkaya
Starting Member

8 Posts

Posted - 04/08/2013 :  06:24:49  Show Profile  Reply with Quote
Thank you for your interest Consent However, the
years as part of the ddmmyy

If possible bird
I would be grateful if it line by line :)

18 - 25 age, 30%
25 - 35 age, 40%
35 - 45 age, 20%
Go to Top of Page

erdogan_ozkaya
Starting Member

8 Posts

Posted - 04/08/2013 :  10:54:09  Show Profile  Reply with Quote
HELP....
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 04/08/2013 :  11:10:22  Show Profile  Reply with Quote
Did you mean this?
SELECT '18-25' as [GROUP], (COUNT(CASE WHEN Age >=18 AND Age<25 THEN Age END)*100)/COUNT(*) AS PERCENTAGE
FROM @Members union all
SELECT '25-35' as [GROUP], (COUNT(CASE WHEN Age >=25 AND Age<35 THEN Age END)*100)/COUNT(*) AS PERCENTAGE
FROM @Members union all
SELECT '35-45' as [GROUP], (COUNT(CASE WHEN Age >=35 AND Age<45 THEN Age END)*100)/COUNT(*) AS PERCENTAGE
FROM @Members union all
SELECT '>=45' as [GROUP], (COUNT(CASE WHEN Age >=45 THEN Age END)*100)/COUNT(*) AS PERCENTAGE
FROM @Members
Go to Top of Page

erdogan_ozkaya
Starting Member

8 Posts

Posted - 04/08/2013 :  11:22:33  Show Profile  Reply with Quote
no,

18-24 years old 50%
Between the ages of 24 to 35 40%
Between the ages of 35 to 45 10%

I want to get the result as

TABLE: MEMBERS

AREAS
NAME,
NAME,
AGE (01.01.1980)

can I query it
I would like to thank you for your interest :) Greetings from Turkey
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 04/09/2013 :  05:35:57  Show Profile  Reply with Quote

DECLARE @TotalCnt INT;
SELECT @TotalCnt = COUNT(*) FROM @Members;
;WITH CTE AS ( SELECT 
		CASE  WHEN Age >=18 AND Age<25 THEN 1
			WHEN Age >=25 AND Age<35 THEN 2
			WHEN Age >=35 AND Age<45 THEN 3
			ELSE 4 END AS AgeGrp
		FROM @Members
	)
SELECT CASE WHEN AgeGrp = 1 THEN COUNT(AgeGrp)*100/@TotalCnt
	   WHEN AgeGrp = 2 THEN COUNT(AgeGrp)*100/@TotalCnt
	   WHEN AgeGrp = 3 THEN COUNT(AgeGrp)*100/@TotalCnt
	   WHEN AgeGrp = 4 THEN COUNT(AgeGrp)*100/@TotalCnt END AvgAge
FROM CTE
GROUP BY AgeGrp
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/09/2013 :  05:43:51  Show Profile  Reply with Quote
My prefered way to do this is to create a AgeRange Table with value Start and End.. This way you'll have flexibility of changing range boundary values or creating or removing ranges etc.

Then in query you just need to add a join to range table on Age BETWEEN Start AND End

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000