Percentage of total
BOONER
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
SELECT day,
leads,
leads / (SELECT COUNT(*) FROM table) As Percent
FROM table
--
Marsh
MVP [MS Access]
BOONER
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
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]
BOONER wrote:
>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?
>
BOONER
is close enough for what I am using the data for.
BOONER
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.
John Vinson
<BOO...@discussions.microsoft.com> wrote:
>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
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
BOONER
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
why it shouldn't work. What does your query look like?
Jamie Collins
John Vinson wrote:
> My guess is that you're adding up Float or Double numbers.
>
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
? ( 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" <jamiec...@xsmail.com> wrote in message
news:1157112697.0...@e3g2000cwe.googlegroups.com...
Jamie Collins
Michel Walsh wrote:
> ? ( CDbl(0.1) + CDbl(0.2) + CDbl(0.3) + CDbl(0.4) ) -1
> 2.77555756156289E-17
This is the 'queries' group, right <g>?
> *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
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" <jamiec...@xsmail.com> wrote in message
news:1157446163....@i3g2000cwc.googlegroups.com...
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
Jamie Collins wrote:
> 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
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
"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:1157464103.2...@p79g2000cwp.googlegroups.com...