Percentage of total

[Guest]

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%

 

[Marshall Barton]

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%

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

 

[Guest]

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?

 

[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?

 

[Guest]

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.

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?

 

[Guest]

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.

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?

 

[John Vinson]

Do you know why this returns 100.1%?

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]

 

[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

 

[Guest]

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%

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.

 

[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?

 

[Jamie Collins]

My guess is that you're adding up Float or Double numbers.

summing 0.1 + 0.2 + 0.3 + 0.4 does NOT equal 1.0

I assume this is a misstatement. It certainly equals 1.0 for me:

SELECT IIF(CDBL(1.0) = CDBL(0.1) + CDBL(0.2) + CDBL(0.3) + CDBL(0.4) ,
'TRUE', 'FALSE')
AS equals_one
;

SELECT IIF(CDBL(1.0) = SUM(DT1.dbl_col), 'TRUE', 'FALSE')
AS equals_one
FROM
(
SELECT DISTINCT CDBL(0.1) AS dbl_col FROM AnyTable
UNION ALL
SELECT DISTINCT CDBL(0.2) FROM AnyTable
UNION ALL
SELECT DISTINCT CDBL(0.3) FROM AnyTable
UNION ALL
SELECT DISTINCT CDBL(0.4) FROM AnyTable
) AS DT1;

Jamie.

--

 

[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

 

[Jamie Collins]

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

*If* you get "true" from SQL, not using Decimal, but Floats, or Double
Precision Floats...

Yes, I do. Simplifying:

SELECT CDBL(0.1) + CDBL(0.2) + CDBL(0.3) + CDBL(0.4)
AS dbl_val
;
SELECT SUM(DT1.dbl_col)
AS dbl_val
FROM
(
SELECT DISTINCT CDBL(0.1) AS dbl_col FROM AnyTable
UNION ALL
SELECT DISTINCT CDBL(0.2) FROM AnyTable
UNION ALL
SELECT DISTINCT CDBL(0.3) FROM AnyTable
UNION ALL
SELECT DISTINCT CDBL(0.4) FROM AnyTable
) AS DT1
;

Both return the value 1 (double float).

...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 ).

In lieu of a Jet specification that would confirm, we merely have
empiricism <g>. Running the query returns the value 1 and Mr Vinson
said it wouldn't. A misstatement, then, right? More usefully, we can
assume such a tolerance exists i.e. we can remove '*may*' from your
statement, right? The question now is: What is the tolerance?

Anyone?

Jamie.

--

 

[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

 

[Jamie Collins]

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).

This is a different issue, I think.

Your example is using fixed point DECIMAL values. The effect you
demonstrate is not exhibited at 1E-18 and below, suggesting that the
DECIMAL is being coerced to double float at the intermediate stage.
Using a stored explicit DECIMAL value, the effect is not seen at all,
even below 1E-18 e.g.

CREATE TABLE Test1 (
dec_col DECIMAL(20, 20) NOT NULL
)
;
INSERT INTO Test1 (dec_col) VALUES (1E-20)
;
SELECT (dec_col + 0.1) - 0.1
FROM Test1
;

returns 1E-20.

I reported a similar oddity where DECIMAL values are coerced to DOUBLE
here:

http://groups.google.com/group/microsoft.public.access/msg/2b99730e4badf9b8

Anyhow, we were talking about floating point, rather than fixed point,
weren't we? Surely no one would dispute that the equivalent fixed point
values would sum to 1 i.e.

SELECT CCUR(0.1) + CCUR(0.2) + CCUR(0.3) + CCUR(0.4)

Jamie.

--

 

[Jamie Collins]

This is a different issue, I think.

While we're OT, how about this for a shocking loss of accuracy:

set rs = CreateObject("ADODB.Recordset") : _
rs.ActiveConnection = CurrentProject.Connection : _
rs.CursorLocation = adUseClient : _
rs.source = "SELECT 1.9 / 10" : _
rs.Open : _
? rs(0)

returns 0.1.

Jamie.

--

 

[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