group by one column but display another

[jtal]

I apologize in advance if I dont use the correct terminology or I'm just lacking some basic understanding (I'm new here).

Lets say I have a query subject "customer" that is defined as:

select id, name
from [datasource1].customer
union all
select id, name
from [datasource2].customer

with the data:

id | name
---------------------------
1  | Peter Venkman
2  | Raymond Stantz
2  | Ray Stantz
3  | Dana Barrett

I also have a query subject "purchase_union":

order_id | customer_id | amount
------------------------------------------
1              1                     $125
2              2                     $50
3              2                     $25
4              3                     $39

And finally, I have two query items:

customer_name -> [customer_union].name
total_amount -> [purchase_union].amount


I'm creating a list report by dragging customer_name and total_amount onto my report.

I am hoping to get only 3 rows back since there are only 3 customers (one customer has two records with different names).
However, the default behavior appears to be a group by name, so I get separate totals for customer ID=2.

In SQL, I would group by customer.id, and then just pick any name from one of those columns (e.g. the first one).

How do I do this in Cognos universe?

Thank you for any help,
Justin

[blom0344]

Firstly, universe is a Business Objects metadata layer, in Cognos this is called a framework. Did you try to use an aggregate on the name (min or max) to ensure 1 row returned for this Stantz fellow  :)?

A well organized datawarehouse should yield 1 and only one name for each Id by the way..