Matching values per ID

[D19PAL]

Hi,

I need to count if people are in the same teams.

So theres  2 blue with 2 different ID so 2

3 PINK so 3

Team,count

Blue, 2

Pink ,3

ID, TEAM,  COUNT

1, BLUE, 1

2, BLUE, 1

3, GREEN,  0

4, RED, 0

5, PINK, 1

6, PINK, 1

7, PINK,1

Thanks

[chrismarlow]

Hi,

Are you trying to calculate COUNT column & return 1 for BLUE and PINK as there are 2 IDs for each (so 2-1=1) and 0 for GREEN and RED as there are 1 ID for each (so 1-1=0)? If so if you had 3 IDs for a TEAM would you show 2?

Cheers,

Chris.

[D19PAL]

Yes I want to know how to create the count field.

Ive shown the table as the outcome.

Thanks

[chrismarlow]

Hi,

So COUNT(TOTAL <TEAM> ID)-1 would get you the count, you will then need to set it not to supress zeros.

Cheers,

Chris.

[D19PAL]

COUNT doesn't exist, but I want to create it, hence how?

Sorry, if it wasn'tt clear.

[chrismarlow]

Hi,

So you want to do this in script? Maybe;

data:
load * inline [
ID, TEAM
1, BLUE
2, BLUE
3, GREEN
4, RED
5, PINK
6, PINK
];

left join(data)
LOAD
	TEAM,
	Count(ID)-1 as COUNT
Resident data
GROUP BY TEAM;

Cheers,

Chris.

[D19PAL]

No, that hasn't worked, I've used test data on here.