Percentage of total

B

BOONER

Okay,

I have a table that contains the number of leads recieved by day. I need a
query that will display the daily leads as a percentage of the total number
of leads recieved (to determine lead arrival paterns). Basically, I need the
third column in the example below:

day leads percent
1 1 10%
2 5 50%
3 4 40%
 
M

Marshall Barton

BOONER said:
I have a table that contains the number of leads recieved by day. I need a
query that will display the daily leads as a percentage of the total number
of leads recieved (to determine lead arrival paterns). Basically, I need the
third column in the example below:

day leads percent
1 1 10%
2 5 50%
3 4 40%
Click to expand...


SELECT day,
leads,
leads / (SELECT COUNT(*) FROM table) As Percent
FROM table
 
B

BOONER

Okay,

I am having a problem becaust this returns percentages that add up to ~
8600%. It should all add up to 100%. Correct me if I am wrong, but the
statement below divides the daily leads by the count of records in the leads
field (which would work if there were only one lead for each record). What I
need to do is to divide the daily leads by the sum of leads in the leads
field. When I try to do this I get an error that states "You tried to
execute a query that does not include the specified expression 'date' as part
of an aggregate function." How can I get around this?
 
M

Marshall Barton

My mistake. That should have been:

SELECT day,
leads,
leads / (SELECT Sum(leads) FROM table) As Percent
FROM table

I have no idea what you are talking about with a date
expression. Is the query supposed to something beyond what
you have explained so far?
 
B

BOONER

Thanks a lot Marshall. Do you know why this returns 100.1%? Either way, it
is close enough for what I am using the data for.

Marshall Barton said:
My mistake. That should have been:

SELECT day,
leads,
leads / (SELECT Sum(leads) FROM table) As Percent
FROM table

I have no idea what you are talking about with a date
expression. Is the query supposed to something beyond what
you have explained so far?
--
Marsh
MVP [MS Access]

I am having a problem becaust this returns percentages that add up to ~
8600%. It should all add up to 100%. Correct me if I am wrong, but the
statement below divides the daily leads by the count of records in the leads
field (which would work if there were only one lead for each record). What I
need to do is to divide the daily leads by the sum of leads in the leads
field. When I try to do this I get an error that states "You tried to
execute a query that does not include the specified expression 'date' as part
of an aggregate function." How can I get around this?
Click to expand...
Click to expand...
 
B

BOONER

One more question.

What if i have another field called "Region" that I want to throw in the
mix. For instance, I would like the same query as below but I would like to
only divide by the sum of leads from the same region.

Marshall Barton said:
My mistake. That should have been:

SELECT day,
leads,
leads / (SELECT Sum(leads) FROM table) As Percent
FROM table

I have no idea what you are talking about with a date
expression. Is the query supposed to something beyond what
you have explained so far?
--
Marsh
MVP [MS Access]

I am having a problem becaust this returns percentages that add up to ~
8600%. It should all add up to 100%. Correct me if I am wrong, but the
statement below divides the daily leads by the count of records in the leads
field (which would work if there were only one lead for each record). What I
need to do is to divide the daily leads by the sum of leads in the leads
field. When I try to do this I get an error that states "You tried to
execute a query that does not include the specified expression 'date' as part
of an aggregate function." How can I get around this?
Click to expand...
Click to expand...
 
J

John Vinson

Do you know why this returns 100.1%?
Click to expand...

My guess is that you're adding up Float or Double numbers.
Floating-point numbers are approximations, not exact - just as the
fraction 1/7 cannot be represented exactly as a decimal number (it's
an infinite repeating value 0.142857142857142857...), so the number
0.1 is an infinite repeating binary fraction.

As a result, summing 0.1 + 0.2 + 0.3 + 0.4 does NOT equal 1.0 - it's a
tiny bit off, since all of the values are truncated. You're running
into the same issue.

John W. Vinson[MVP]
 
M

Marshall Barton

I can't be sure because your example doesn't include that
data. Maybe, it's as simple as:

SELECT day,
leads,
leads / (SELECT Sum(X.leads) FROM table As X
WHEREX.Region = table.Region) As Percent
FROM table
 
B

BOONER

I tried the query below but it locks up my computer when I run it. I can
view the results but not scroll through them or export them.

The region field is part of the same table and I need the percent column
shown below.

Region day leads percent
A 1 5 50%
A 2 5 50%
B 3 4 100%


Marshall Barton said:
I can't be sure because your example doesn't include that
data. Maybe, it's as simple as:

SELECT day,
leads,
leads / (SELECT Sum(X.leads) FROM table As X
WHEREX.Region = table.Region) As Percent
FROM table
--
Marsh
MVP [MS Access]

What if i have another field called "Region" that I want to throw in the
mix. For instance, I would like the same query as below but I would like to
only divide by the sum of leads from the same region.
Click to expand...
Click to expand...
 
M

Marshall Barton

Other than a missing space between WHERE and X, I don't see
why it shouldn't work. What does your query look like?
 
M

Michel Walsh

Hi,



? ( 0.1 + 0.2 + 0.3 + 0.4 ) -1
2.77555756156289E-17

? ( CDbl(0.1) + CDbl(0.2) + CDbl(0.3) + CDbl(0.4) ) -1
2.77555756156289E-17

? ( CDec(0.1) + CDec(0.2) + CDec(0.3) + CDec(0.4) ) -1
0



*If* you get "true" from SQL, not using Decimal, but Floats, or Double
Precision Floats, that *may* be because the involved test compares with a
built-in zero-tolerance (ie, if the absolute value of the number is less
than the tolerance, then the number *is* zero ).


Hoping it may help,
Vanderghast, Access MVP
 
M

Michel Walsh

Hi,


There is also the case that is dependant of the order of the evaluation:


? CurrentProject.Connection.Execute("SELECT
1E-16+(0.1-0.1)").Fields(0).Value
1E-16
? CurrentProject.Connection.Execute("SELECT
(1E-16+0.1)-0.1").Fields(0).Value
9.71445146547012E-17



where, in the second case, we have, for intermediate result, to hold a value
with high precision over 15 order of magnitude (1E-1 and 1E-16).


As far as iif is implied, it seems it rounds the fraction to an integer
before making the test:

? CurrentProject.Connection.Execute("SELECT iif(.5, 'true',
'false')").Fields(0).Value
false
? CurrentProject.Connection.Execute("SELECT iif(.5+1E-16, 'true',
'false')").Fields(0).Value
true




Vanderghast, Access MVP
 
M

Michel Walsh

Hi,


and the adUseClient is required, since without it, the result is as
expected. Quite bizarre. First time I got confronted to this.

Vanderghast, Access MVP
 
Top