Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
IanKM
Starting Member
18 Posts |
 Posted - 2011-11-09 : 04:42:49
|
| Hi there Im trying to write a query to find out how many people on my database are under 61 and ive come up with this which doesn't work Select result = count(paxid) - (SELECT count(*) FROM pax WHERE type = 'p' AND iscurrent = 1) from pax Where dbo.CalculateAge(DateOfBirth,'09/11/2011') < 61I have italisised the area of code which is causing the problem but i need at least some way of gathering the total number of current pax Many thanks Ian |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-11-09 : 04:46:25
|
You don't have to math. Just count.select count(*) from table where condition
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
IanKM
Starting Member
18 Posts |
Posted - 2011-11-09 : 05:04:26
|
| sorry i meant percentage of people not how many.so ill need to do a little bit of math |
|
|
|
IanKM
Starting Member
18 Posts |
Posted - 2011-11-09 : 06:07:54
|
| ok now ive got thisSelect [upto60] = (Select count(paxid) from pax Where dbo.calculateAge(DateOfBirth,'09/11/2011') < 61) / (Select Count(*) From pax where type = 'p' and iscurrent = 1) * 100.0 but... the result comes out as zero i suspect because the counts produce a integer not a double so when i come to muliply the 100.0 im multiplying by 0 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-09 : 06:27:47
|
shouldnt it beSelect [upto60] = (Select count(paxid) from pax Where dbo.calculateAge(DateOfBirth,'09/11/2011') < 61)* 100.0 / (Select Count(*) From pax where type = 'p' and iscurrent = 1) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
IanKM
Starting Member
18 Posts |
Posted - 2011-11-09 : 09:04:49
|
| that is awesome thanks a lot |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-09 : 09:06:50
|
| wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Math Functions