[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 136
  • Last Modified:

How do I join two tables and group by a field called 'Project'?

How do I write a query to join two tables and group them?
0
VBBRett
Asked:
VBBRett
  • 2
1 Solution
 
Patrick MatthewsCommented:
Depends on the tables, of course.  Please post the structure of the two tables involved.
0
 
VBBRettAuthor Commented:
I just want a basic example that's all.
0
 
Patrick MatthewsCommented:
SELECT <some fields>
FROM Table1 INNER JOIN
    Table2 ON <some fields>
GROUP BY <something>
0
 
dbbishopCommented:
Any columns that you name in your SELECT list that are not aggregate functions (e.g. COUNT(), MIN(), MAX(), SUM()) must be named in the GROUP BY clause.

The JOIN is usually done on matching primary/foreign keys.

example:
Employee
EmpID, LastName, FirstName, SSN, HireDate

License
LicenceID
EmpID
LicenseName
ExpireDate

SELECT LastName, FirstName, LicenseName, ExpireDate
FROM Employee JOIN License ON Employee.EmpID = License.EmpID


With grouping, lets say yo wanted a count of licenses by employee, you'd use:

SELECT EmpID, LastName, FirstName, COUNT(*) AS [Count]
FROM Employee JOIN License ON Employee.EmpID = License.EmpID
GROUP BY EmpID, LastName, FirstName

EmpID, LastName and FirstName all need to be in the GROUP BY clause since there is no aggregate function used with them. You can bypass this in a case like above, by using something like:

SELECT EmpID, MAX(LastName) AS LastName, MAX(FirstName) AS FirstName, COUNT(*) AS [Count]
FROM Employee JOIN License ON Employee.EmpID = License.EmpID
GROUP BY EmpID

since, for any given EmpID the last name and first name will ALWAYS be the same.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now