Return One Record per Student Athlete in Query

[wwconslt]

Hello Everyone,

Cognos 10.1.1 with relational data (Informix)

Currently I have a query of student athletes that returns the following results.  I'm filtering on Activity Type = Athlete

ID    Name             Activity     Activity Type

1    John Jones      Baseball   Athlete
1    John Jones      Football    Athlete
1    John Jones      Track        Athlete
2    Mary Smith      Lacrosse  Athlete
2    Mary Smith      Softball    Athlete

What I'm trying to do is only return one record in the query for each athlete, regardless of how many sports they play.  This is in a query that will be joined to another query for the final list report.

I'd like the data to look like this:

ID  Name          Activity Type

1   John Smith   Athlete
2   Mary Smith   Athlete

Any help is appreciated!

[MFGF]

Hi,

Just remove the Activity item from your query. The result should then group exactly as you hope?

MF.

[wwconslt]

Thanks MFGF.  That worked!  I wish I knew why it worked :)

[Lynn]

Thanks MFGF.  That worked!  I wish I knew why it worked :)

When a relational query has the "auto group and summarize" property set to Yes, which is the default, then the query will be generated as a select distinct or a select with a group by for measures based on elements present in the layout.

[navissar]

You ask to see the activity per athlete, so you will, and if an athlete has more than one activity, the result would repeat that athlete. That's the way relational dbs work...