aggregate query

[Guest]

Hi,

I am trying to write a query that would allow me to calculate the proportion
of a specific value in a field against against the sum of all the values in
that field. In other words something like:

select balance / sum (balance) from table where account_type = 'Line of
Credit' and [some other conditions and joins]

This syntax does not work obviously and I am not sure how to structure such
a query.

To clarify, if there are 5 records of the given type, then I would get a
dataset of 5 records/numbers, the sum of which will give me 1. So, if the
records are 1,2,3,4,5, then I would get 1/(1+2+3+4+5), 2/sum(1..5),
3/sum(1..5), etc.

Thanks.

 

[Michel Walsh]

SELECT balance / (SELECT SUM(balance)
FROM table
WHERE accountType='x')
FROM table
WHERE accountType = 'x'



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks, Michael. The problem is that account type should come from another
table which should be linked to this table. Like so:

SELECT balance
FROM table1 INNER JOIN table2 on table1.product_id = table2.product
WHERE table2.code = 'Line of Credit'

Is there a way to join the product type table on the outer FROM clause, then
join the main table to itself in the subquery, or would I need to join the
product table to both the main table and the subquery separately?

SELECT balance / (SELECT SUM(balance)
FROM table
WHERE accountType='x')
FROM table
WHERE accountType = 'x'



Hoping it may help,
Vanderghast, Access MVP

Hi,

I am trying to write a query that would allow me to calculate the
proportion
of a specific value in a field against against the sum of all the values
in
that field. In other words something like:

select balance / sum (balance) from table where account_type = 'Line of
Credit' and [some other conditions and joins]

This syntax does not work obviously and I am not sure how to structure
such
a query.

To clarify, if there are 5 records of the given type, then I would get a
dataset of 5 records/numbers, the sum of which will give me 1. So, if the
records are 1,2,3,4,5, then I would get 1/(1+2+3+4+5), 2/sum(1..5),
3/sum(1..5), etc.

Thanks.

 

[Guest]

This is the query that I have.

PARAMETERS customer_id Long;
SELECT customer_product.id, customer.demand_deposits *
customer_product.average_balance/(select sum(average_balance) from
customer_product inner join product on product.id =
customer_product.product_id where product.code = 'LOC' and
customer_product.customer_id = [customer_id]) AS fraction
FROM product INNER JOIN (customer_product INNER JOIN customer ON
customer_product.customer_id = customer.id) ON product.id =
customer_product.product_id
WHERE (((product.code)='LOC') AND
((customer_product.customer_id)=[customer_id]));

Before joining the customer table, it worked fine (got 2 records back as
expected), but now I get 20 records back when there are only 2 products of
type 'LOC' for the customer I am looking at.

Any ideas how to fix this?

Thanks.

SELECT balance / (SELECT SUM(balance)
FROM table
WHERE accountType='x')
FROM table
WHERE accountType = 'x'



Hoping it may help,
Vanderghast, Access MVP

Hi,

I am trying to write a query that would allow me to calculate the
proportion
of a specific value in a field against against the sum of all the values
in
that field. In other words something like:

select balance / sum (balance) from table where account_type = 'Line of
Credit' and [some other conditions and joins]

This syntax does not work obviously and I am not sure how to structure
such
a query.

To clarify, if there are 5 records of the given type, then I would get a
dataset of 5 records/numbers, the sum of which will give me 1. So, if the
records are 1,2,3,4,5, then I would get 1/(1+2+3+4+5), 2/sum(1..5),
3/sum(1..5), etc.

Thanks.

 

[Michel Walsh]

Rather than making a correlated sub-query, you can do it in two step, first:

SELECT SUM(balance) AS theSum, product_id
FROM table1
GROUP BY product_id


assume it is saved as q1, then,


SELECT balance, balance/ q1.theSum
FROM (table1 INNER JOIN table2 on table1.product_id = table2.product)
INNER JOIN q1 ON table1.product_id=q1.product_id


Hoping it may help,
Vanderghast, Access MVP

Thanks, Michael. The problem is that account type should come from another
table which should be linked to this table. Like so:

SELECT balance
FROM table1 INNER JOIN table2 on table1.product_id = table2.product
WHERE table2.code = 'Line of Credit'

Is there a way to join the product type table on the outer FROM clause,
then
join the main table to itself in the subquery, or would I need to join the
product table to both the main table and the subquery separately?

SELECT balance / (SELECT SUM(balance)
FROM table
WHERE accountType='x')
FROM table
WHERE accountType = 'x'



Hoping it may help,
Vanderghast, Access MVP

Hi,

I am trying to write a query that would allow me to calculate the
proportion
of a specific value in a field against against the sum of all the
values
in
that field. In other words something like:

select balance / sum (balance) from table where account_type = 'Line of
Credit' and [some other conditions and joins]

This syntax does not work obviously and I am not sure how to structure
such
a query.

To clarify, if there are 5 records of the given type, then I would get
a
dataset of 5 records/numbers, the sum of which will give me 1. So, if
the
records are 1,2,3,4,5, then I would get 1/(1+2+3+4+5), 2/sum(1..5),
3/sum(1..5), etc.

Thanks.

 

[Michel Walsh]

That is the nature of the join to eventually amplify the original number of
records. If tableA has N records, and tableB has M records, then


SELECT tableA.*
FROM tableA, tableB


will produce N * M records, where each of the N records of tableA would
appear M times.


If you use an INNER join, and that one of the field implied in the ON clause
has duplicated value, you will also have amplification of the original
record from the other table. With

ON customer_product.customer_id = customer.id

if there are N records in customer_product having the value customer_id,
then each of the record of customer, having the same id, will be repeated N
times, OR, another way to see it, if a given id appear M times in customer,
then each record in customer_product having the SAME customer_id value, will
now appear M times.

To avoid unsuspected surprises, at least one of the tables involve should
not have duplicated values in the implied field. That can be achieved with a
GROUYP BY clause, if not otherwise:

FROM
(SELECT id, LAST(otherField) FROM table GROUP BY id ) AS employee
INNER JOIN .... ON employee.id = ....



since we have grouped by on id, there is no dup for this value. Note there
are cases where the amplification IS wanted, so do NOT read that we must
ALWAYS avoid duplicated values in both implied tables. It is just that when
that occurs, if you are not suspecting it, you may get the surprises... that
you got, such as having 20 records when only 2 were expected!




Hoping it may help,
Vanderghast, Access MVP

This is the query that I have.

PARAMETERS customer_id Long;
SELECT customer_product.id, customer.demand_deposits *
customer_product.average_balance/(select sum(average_balance) from
customer_product inner join product on product.id =
customer_product.product_id where product.code = 'LOC' and
customer_product.customer_id = [customer_id]) AS fraction
FROM product INNER JOIN (customer_product INNER JOIN customer ON
customer_product.customer_id = customer.id) ON product.id =
customer_product.product_id
WHERE (((product.code)='LOC') AND
((customer_product.customer_id)=[customer_id]));

Before joining the customer table, it worked fine (got 2 records back as
expected), but now I get 20 records back when there are only 2 products of
type 'LOC' for the customer I am looking at.

Any ideas how to fix this?

Thanks.

SELECT balance / (SELECT SUM(balance)
FROM table
WHERE accountType='x')
FROM table
WHERE accountType = 'x'



Hoping it may help,
Vanderghast, Access MVP

Hi,

I am trying to write a query that would allow me to calculate the
proportion
of a specific value in a field against against the sum of all the
values
in
that field. In other words something like:

select balance / sum (balance) from table where account_type = 'Line of
Credit' and [some other conditions and joins]

This syntax does not work obviously and I am not sure how to structure
such
a query.

To clarify, if there are 5 records of the given type, then I would get
a
dataset of 5 records/numbers, the sum of which will give me 1. So, if
the
records are 1,2,3,4,5, then I would get 1/(1+2+3+4+5), 2/sum(1..5),
3/sum(1..5), etc.

Thanks.

 

[Guest]

I actually found the problem. It had to do with my parameter having the same
name as one of the table fields. I am still not sure how Access manages to
screw things up without ever giving any errors, but changing the parameter
name to something else solved the issue.

To comment on your suggestions, join conditions should be constructed on
primary or foreign keys. Hence if you structure the join properly, there
should be no surprises. Joins do not have a "nature of amplifying" - it's all
about how you construct the join condition.

Hope that helps.