Need to somehow Sum (Group By)

[Cage]

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.

 

[jebry]

Hey Cage!

Air code here:

Select Person, [Company Count], Sum(Revenue) As [Total Revenue] From YourTable Inner Join (Select Person, Count(Company) AS [Company Count] From (Select Person, Company From YourTable Group By Person, Company) Group By Person) As A On YourTable.Person = A.Person Group By Person, [Company Count]

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[mp9]

SELECT Person, Count(Company) AS CountOfCompany, Sum([Market Revenue]) AS SumOfMarketRevenue
FROM YourTableNameHere
GROUP BY Person;


...asumes yoru fields are called Person, Company and Market Revenue.

http://www22.brinkster.com/accessory/

 

[mp9]

Ignore my last post, I've mis-read the question...

 

[jebry]

Hi again!

This seems to be simpler:

Select Person, Count(Company) As [Company Count], Sum(SumOfRevenue) As [Total Revenue] From (Select Person, Company, Sum(Revenue) As SumOfRevenue From YourTable) Group By Person Company

It's still air code!

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[AlexCuse]

What is happening is not what you think, it is simply counting the 'company' fields where Person = person. Because there is one for each market, it is counted twice. You need to use a subquery:

SELECT a.person, count(a.company), sum(a.revenue)
FROM (SELECT person, company, sum(revenue) FROM tblName GROUP BY person, company) a
GROUP BY a.person

Hope this helps,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[jebry]

Hey Alex!

I'm glad you agree with me since I hadn't bothered testing my SQL!

Thanks

Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[AlexCuse]

I had to test it, because I am more familiar with the syntax for SQL Server. I embarrass myself enough with my own questions ;-)



It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[Golom]

I gather that you want a count of the DISTINCT companies. Try this

SELECT P.Person
     , Q.[Company Count]
     , Sum(Revenue) As [Total Revenue]

FROM myTable P INNER JOIN 
     (Select Person, Count(*) As [Company Count]
      From (Select DISTINCT Person, Company From myTable X)
      Group by Person) Q
     ON P.Person = Q.Person

Group By P.Person,  Q.[Company Count]

 

[Cage]

Alex, what is the difference between the 'a' (after the FROM statement) and tblName.
e.g

FROM (SELECT person, company, sum(revenue) FROM tblName GROUP BY person, company) a <----

If I try enter 'a' as the same table as tblName I receive the error:

The specified field '[tblMyTable].[Company]' could refer to more than one table listed in the FROM clause of your SQL statement.

 

[Cage]

I want to count the number of companies per Person regardless of how many times they appear. The reason they appear more than once it because the market changes.

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 3 105
Ali 1 25

 

[jebry]

Hi!

I made a mistake:

Select Person, Count(Company) As [Company Count], Sum(SumOfRevenue) As [Total Revenue] From (Select Person, Company, Sum(Revenue) As SumOfRevenue From YourTable Group By Person, Company) Group By Person

sorry


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[lespaul]

I want to count the number of companies per Person regardless of how many times they appear.

then your example is wrong. Based on what you say above, Jim has 3 companies, Bob has 5 companies and Ali has 1.

As Golom suggested, and your example shows, counting DISTINCT Companies: Jim has 2 distinct companies A & B, Bob has 3 distinct companies a b & c and Ali still only has 1 distinct company.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[AlexCuse]

the 'a' is an alias (if to prevent problems due to the fact that you are using same field/table differently in the query and subquery). Did you add this part correctly?

FROM (SELECT person, company, sum(revenue) FROM tblName GROUP BY person, company) [b]a[/b]
[code]

If you don't make it clear to Access that a refers to the subquery, it will make it throw up.

Hope this Helps,

Alex



 

It's a magical time of year in Philadelphia.  Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[AlexCuse]

D'oh, slight change

SELECT a.person, count(a.company), [b]sum(a.sumrevenue)[/b]
FROM (SELECT person, company, sum(revenue) [b]AS sumrevenue[/b] FROM tblName GROUP BY person, company) a
GROUP BY a.person

sorry about that!

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.