Using percentages in SQL 3

[Mayoor]

Hi I want to calculate the count colum in this SQL as a percentage of the total number of records

so far I have this

SELECT Force1, Count(force1) as Count
FROM applicants


I would like the count column to e expressed as a percentage.

Please can you help?

Mayoor

 

[hneal98]

SELECT Force1, Count(force1) as Count
FROM applicants


from what I can see from the above query, you will get 1 for the count on all lines because you are counting the field you are listing. You need to be more specific as to the type of data in Force1. Is it a count or sum of something? If so, you could do something like this:

SELECT (Sum(Force1)/Count(*))*100 FROM applicants

 

[Mayoor]

ok with this

SELECT (Sum(Force1)/Count(*))*100 as pies
FROM applicants

Im getting this error

Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a varchar data type as an argument.

any ideas how I convert the varchar data to a integer type?

 

[hneal98]

Again, I did not know what kind of data was in Force1. Obviously you can not do a sum on a varchar. If that is a number, you will have to convert it. If it is an integer, you would wrap the field in the convert statement like this:

SELECT (Sum(convert(integer,Force1))/Count(*))*100 as pies
FROM applicants

 

[Mayoor]

Force1 is a field which records the location of the person. I would like to group by location and count instances of the location in each records and produce output like this.

Percentage
Location 1 45
Location 2 30
Location 3 20
Location 4 5
.
.
.
.

 

[hneal98]

Ohhh. That makes a big difference. Here is what you need to do:

SELECT
Force1,
((select count(*) from applicants)/Count(*))*100 as pies
FROM applicants app
Group by Force1

 

[Mayoor]

Nope thats not working either

the following statement SELECT
Force1,
((select count(*) from applicantdata)/Count(*))*100 as pies
FROM applicantdata
Group by Force1

returns

Pies
Location 1 5800
Location 2 12800
Location 3 6600
Location 4 8800
.
.
.
.
and so on these are no percentages. All I want it to do is divide the count column by the total numer of records on the database and muliply the column by 100.

 

[onpnt]

how bout

SELECT
Force1,
((select count(Force1) from applicantdata group by Force1)/Count(*))*100 as pies
FROM applicantdata
Group by Force1

I may be missing the logic of the fields in use though

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page

 

[hneal98]

Sorry, I think I see what the problem is:

SELECT
Force1,
(Count(*)/(select count(*) from applicants))*100 as pies
FROM applicants app
Group by Force1

I had them switched around. Try it this way.

 

[Mayoor]

Hi when I use this

SELECT
Force1,
(Count(*)/(select count(*) from applicants))*100 as pies
FROM applicantdata
Group by Force1


it gives me zeros in every column, it may be to do with the fact that I have to declare the column which will accept decimals, does anyone know how to do this?

 

[hneal98]

Part of it is you are probably dealing with some large numbers, but the other part is that convert does not alwasy do what you think it is. Try this instead.

SELECT
Force1,
convert(decimal(5,2),(cast(Count(*) as real)/cast((select count(*) from applicants) as real)*100) as pies
FROM applicantdata
Group by Force1

 

[starbuckinfinity]

thanks, hneal98, you are awesome! you helped me so much! this was exactly what I was searching for! Thanks!

 

[SQLSister]

SELECT 
    Force1, 
        1.0*(Count(*)/(select count(*) from applicants))*100 as pies 
FROM applicants app
Group by Force1

This is a simpler version. One of the quirky things about T-SQL is that when you divide, it takes the datatype of the top number. So if you convert it to a decimal by using cast or convert or simply by multiplying by 1.0, then you will have a decimal result.


Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[hneal98]

That is great info. I didn't know that myself.

Thanks.