I have the following table:
Person Company Market Revenue
Jim CoyA US 10
Jim CoyA EU 20
Jim CoyB US 20
Bob CoyA US 30
Bob CoyA EU 15
Bob CoyB US 20
Bob CoyB EU 30
Bob CoyC US 10
Ali CoyB UK 25
I wish to obtain the following using a query
Person CompanyCount Revenue
Jim 2 50
Bob 2 105
Ali 1 25
e.g CompanyCount is how many companies they deal with regardless of market.
I have tried using adding a count, however because of the change in market it counts the company more than once, e.g
3, 5 & 1 for Jim, Bob and Ali respectively.
thanks
Cage.
Person Company Market Revenue
Jim CoyA US 10
Jim CoyA EU 20
Jim CoyB US 20
Bob CoyA US 30
Bob CoyA EU 15
Bob CoyB US 20
Bob CoyB EU 30
Bob CoyC US 10
Ali CoyB UK 25
I wish to obtain the following using a query
Person CompanyCount Revenue
Jim 2 50
Bob 2 105
Ali 1 25
e.g CompanyCount is how many companies they deal with regardless of market.
I have tried using adding a count, however because of the change in market it counts the company more than once, e.g
3, 5 & 1 for Jim, Bob and Ali respectively.
thanks
Cage.