Aggregation problem in Report Designer

Hi!!! Please help me.

I have the following table structure.

-A (name)

--B (name)

--C (name, total)

For example.

A { Tom, Sam John }

B {Mazda, Audi, Ford }

C: { (Monitor, 100), (Telephone, 230), (Mouse, 370)}

The corresponding sql select:

select * from A left join B left join C

Retrive obvios result:

And now I have desing report with the following structure:

1) Create list element (A_List) and use detail group to grouping data by A.name in it.

2) Then I use 2 another lists and placed it in A.

Now I want get the textbox = Sum(C.total) in the A_List area. It is obvious that ealier represented sql select make cartesian product (AxBxC) of A,B,C tables. And now I have multiple record for single row for each row in each table. For example I have three equals records for totals.

And I could't use aggregation function in Sum. Anobody know how this problem solves?

Hi,

I'm not sure I'll be answering exactly what you need, but I'll take the risk ;-)

I will try using a RunningTotal, checking for the condition of change, or, in the worst case creating a function in Report code to perform the custom sum.

You'll find more info in Books On Line.

HTH

Jordi Rambla

Solid Quality Learning

aggregate question

In the below structure, if I wanted to get the Id of the comment for
each Generic record having the latest comment time, how would I do that
not using a subquery?

Table: Generic
Id
Description

Table: Comment
Id
GenericId
CommentTime

Currently I have something like the following:

Select
Generic.Id, Max(Comment.CommentTime) /*,Comment.Id for max comment
time comment record*/
From
Generic
INNER JOIN Comment ON Generic.Id = Comment.GenericId
Group By
Generic.Id

To get it, I could do a sub query, using the above query as its source
and joining on the max comment time, but I was wondering if there was a
way to do it without a sub query. Keep in mind that I am looking for a
set of Generic records and not looking for only a single record (so
select top top 1 with order by won't work)You need to take what you have and use it as a subquery as you've
suggested. Other than the subquery, there's no way in SQL to say "give
me the id column from the right table where some other column in the
right table has it's max value".

The reason you can't do that is simple: what id would you get back
from the right table if the maximum value occured in more than one row?|||On 3 Nov 2005 08:41:10 -0800, pb648174 wrote:

>In the below structure, if I wanted to get the Id of the comment for
>each Generic record having the latest comment time, how would I do that
>not using a subquery?
>Table: Generic
>Id
>Description
>Table: Comment
>Id
>GenericId
>CommentTime
>Currently I have something like the following:
>Select
> Generic.Id, Max(Comment.CommentTime) /*,Comment.Id for max comment
>time comment record*/
>From
> Generic
> INNER JOIN Comment ON Generic.Id = Comment.GenericId
>Group By
> Generic.Id
>To get it, I could do a sub query, using the above query as its source
>and joining on the max comment time, but I was wondering if there was a
>way to do it without a sub query. Keep in mind that I am looking for a
>set of Generic records and not looking for only a single record (so
>select top top 1 with order by won't work)

Hi pb648174,

You can do this in two ways.

1. Using a correlated subquery (probably the solution you already had in
mind, since you write: "if there was a way to do it without a sub
query", but I'll give it anyway)

SELECT g.Id, c.CommentTime, c.Id
FROM Generic AS g
INNER JOIN Comment AS c
ON c.GenericId = g.Id
WHERE c.CommentTime = (SELECT MAX(c2.CommentTime)
FROM Comment AS c2
WHERE c2.GenericId = c.GenericId)

2. Using a derived table. This is a subquery as well, but it's not
correlated, and it's used in the FROM clause, in place of a table or
view name:

SELECT g.Id, c.CommentTime, c.Id
FROM Generic AS g
INNER JOIN (SELECT GenericId, MAX(CommentTime) AS MaxCommentTime
FROM Comment
GROUP BY GenericId) AS c2
ON c2.GenericId = g.Id
INNER JOIN Comment AS c
ON c.GenericId = g.Id
AND c.CommentTime = c2.MaxCommentTime

(Note: both queries are untested - see www.aspfaq.com/5006 if you prefer
a tested reply)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you had done this right and realized tht ther are
no magical, universal "id' things in RDBMS, would the schema look like
this?

CREATE TABLE Generic
(generic_id INTEGER NOT NULL PRIMARY KEY,
description VARCHAR(30) NOT NULL,
..);

CREATE TABLE Comments
(generic_id INTEGER NOT NULL
REFERENCES Generic (generic_id)
comment_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
-- where is the comment??
PRIMARY KEY (generic_id, comment_time));

>> but I was wondering if there was a way to do it without a subquery. <<

No, not unless you move comment time into Gerneric.|||Thanks, Celko. Helpful and on-topic as always.