Group it like this!?

[Susch]

Hello there,
I've got this:

Username Ticket counter
dave ticketx 1
dave ticketx 1
david tickety 1
david ticketz 1

and the output should be this:
Username counter
dave 1
david 2

In SQL it's possible with the command: distinct.
Is there any function which is nearly the same in Cognos Impromptu?
Please, can somebody help me with this?

 

[GJParker]

Open the query dialog -> Filter tab -> check eliminate duplicate rows.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[navshiv]

Would you not group Username and then count(Ticket) ?

 

[griffindm]

Actually, I think she would want to group on Username and then get count(distinct Ticket).

Dave G.


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[Susch]

Hey there,

First of all, thanks for your answers!
But sorry, your suggestions don't run at all. The result is always, that impromptu counts every ticket. The result:

Username Ticket Calculation ("count(Ticket)")
dave ticketx 2
david tickety 2
ticketz

but the output should be this:
Username counter
dave 1
david 2

Is there any possibilty to use the distinct in the calculation so that the double tickets can be eliminated in the count?

I would be grateful for your answers!

 

[navshiv]

Group Name and Ticket. New calculation will be Count(Ticket) and associate this with name. Should give you what you're after.

 

[Susch]

Hey there,

Sorry, but it doesn't work, at all! The result is still the same, I wrote before.
The count is wrong. It counts every ticket, which is listed, but it should could only the number of diffrent tickets.

I'd be grateful for your answers!

 

[mayo04]

Hi,

Try this

select username, count(distinct ticket) from yourtable
group by username

Mayoman