Aggregation before QuerySet

[CoalEs]

I have 2 Query Subject object with set of fact data items.
And I have 1 Query Set, that use this Query Subjects for union.
I get result like this:

Code Select Expand

select q.a, q.b, sum(q.c) from (
select a, b, c from w
union
select a, b, c from ww
) q
group by q.a, q.b

I want this:

Code Select Expand

select q.a, q.b, sum(q.c) from (
select a, b, avg(c) from w group by a, b
union
select a, b, avg(c) from ww group by a, b
) q
group by q.a, q.b

Aggregate rollup is set up.

[blom0344]

Apart from the actual solution, I was wondering what you want to achieve by summarizing averages this way, ESPECIALLY from a unioned set. The Union will remove duplicates based on ALL columns, including the average calculation. You will get data that is mighty hard to understand as the removal of duplicates is done for certain records and not for others.   A Union All may solve that, but still why totalize averages??

[CoalEs]

This sql demand due layered data.
I have a full understanding of what happens, thanks. I simplified sql result for concentration on my problem.
My question is how to get Cognos aggregate inside embedded Query Subject.

[CoalEs]

Ok, let's simplify more.
I make one Query Subject with max aggregation.
Then I make second Query Subject, that contains first.
And set min aggregation in it.

Making this I want to get:

select min(q) from
(
select max(q) from ww
)

But I get this:

select min(q) from  ww

I know that this manipulations works in Report Studio Query.
But I want to get it in FW model.
Explain me why works the latest aggregation and how to force a nested aggregation?

[blom0344]

One thing you could try is setting the SQL generation to 'As view' instead of the default 'Minimized' enforce Cognos to build the full SQL code. Minimized may in this case cause a rewrite to what Cognos thinks is sufficient.  The second option is to define an SQL query subject by using Code.

Sorry for asking about your goal.  ;)

[CoalEs]

I made this operations. SQL changed. But data set is not, because meaning of the query has not changed

SQL before:

Code Select Expand

with xx as (
select a, b, c, q from ww
)
select a, b, c, min(q) from xx

SQL after (as View):

Code Select Expand

select a, b, c, q from
(
select a, b, c, min(q) as q from ww
)