Folks,
One more rule that you should keep in mind when using GROUP BY is that although each column name in SELECT must appear in GROUP BY clause, the reverse is not required i.e. you can include column names in GROUP BY clause which are NOT in SELECT clause.
Girish
| [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED] 06/24/2003 09:00 AM
|
To: [EMAIL PROTECTED] cc: Subject: RE: [KCFusion] an easy one |
Adaryl,
Mark has given you pretty good clarification (although DISTINCT in conjunction with GROUP BY can be used and won't give you error - but is not necessary as pointed out by Mark - it was an oversight on my part). The values within [ ] are the field names or table names (the key is to make sure that the SELECT clause must be carefully weaved in with the GROUP BY clause to meet ANSI standards - some rules for SELECT when using GROUP BY are
a) only column names and/or
b) aggregate functions and/or
c) constants
are permitted. Also, all column names within the SELECT statement must be reused within the GROUP BY clause.
Keeping these simple rules in mind would eliminate lot of frustration.
Real Life Examples
-------------------------------
Tthe following SQLs will work on a Windows plateform and you may need to tweak for specific database that you are using,
Here is a complex GROUP BY query,
SELECT [JobNumber] & "-" & [CostType] & "-" & [CostCode1] & [CostCode23] & [CostCode45] & [CostCode67] AS [Job Cost Number], Sum(Hours.HoursWorked) AS [Sum Of HoursWorked], Submitted.WeekEndingDate
FROM (Employee INNER JOIN Submitted ON Employee.EmployeeID = Submitted.EmployeeID) INNER JOIN Hours ON (Employee.EmployeeID = Hours.EmployeeID) AND (Submitted.EmployeeID = Hours.EmployeeID)
GROUP BY [JobNumber] & "-" & [CostType] & "-" & [CostCode1] & [CostCode23] & [CostCode45] & [CostCode67], Submitted.WeekEndingDate, Hours.EmployeeID, Hours.WeekEndingDate, Hours.TimeType, Hours.PayType, Hours.JobNumber, Hours.CostType, Hours.CostCode1, Hours.CostCode23, Hours.CostCode45, Hours.CostCode67, Submitted.EmployeeID, Employee.EmployeeID, Employee.FirstName, Employee.LastName, Employee.UserID, Submitted.WeekEndingDate
HAVING (((Hours.EmployeeID) In (Select EmployeeID From Submitted Where WeekEndingDate Between #6/27/99# And #7/4/99#)) AND ((Hours.WeekEndingDate) Between #6/27/1999# And #7/4/1999#));
Here is a simpler example,
SELECT Hours.PayType, [JobNumber] & "." & [CostType] & "." & [CostCode1] & [CostCode23] & [CostCode45] & [CostCode67] AS [Job Cost Number], Sum(Hours.HoursWorked) AS [Total Hours]
FROM Hours, Submitted
WHERE (((Hours.EmployeeID)=[Submitted].[employeeID]) AND ((Hours.WeekEndingDate)=[Submitted].[WeekEndingDate] And (Hours.WeekEndingDate) Between #9/30/2001# And #9/30/2001#) AND ((Submitted.WeekEndingDate) Between #9/30/2001# And #9/30/2001#))
GROUP BY Hours.PayType, [JobNumber] & "." & [CostType] & "." & [CostCode1] & [CostCode23] & [CostCode45] & [CostCode67];
Here is an example that makes use of DISTINCT
SELECT LastName, FirstName FROM Employee
WHERE EmployeeID In (Select DISTINCT(EmployeeID) From Hours Where WeekEndingDate = #" & weeksEndingDate & "#)
AND ActiveStatus = True
ORDER BY LastName, FirstName;
Hope that helps,
Girish
| "Boles, Mark E" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 06/24/2003 12:17 AM | To: <[EMAIL PROTECTED]> cc: Subject: RE: [KCFusion] an easy one |
also...
> SELECT DISTINCT table1.studentID, table2.goalID
> FROM table1, table2
> WHERE table1.studentID = table2.studentID
the distinct behavior...
I believe this should pull back a distinct studentID meaning which ever goalID it come to first is the one you see unless ordered. You should only get 1 studentID per student on this distinct select.
so your group by would look like:
SELECT table1.studentID, table2.goalID (no need for distinct in a group by - in fact it should give you an error)
FROM table1, table2
WHERE table1.studentID = table2.studentID
HAVING (if desired)(does NOT have to be part of the select)
GROUP BY table1.studentID, table2.goalID
ORDER BY table1.studentID, table2.goalID (If Desired)
NOTE: The fields in the group by must match the selected. so if you say trim(field) in the select you must say trim(field) in the group by. else you will get an error like... _expression_ not a grouped _expression_.... or something real helpful like that.. (sarcasm)
Enjoy,
-MEB
-----Original Message-----
From: Adaryl Wakefield [mailto:[EMAIL PROTECTED]
Sent: Monday, June 23, 2003 5:46 PM
To: [EMAIL PROTECTED]
Subject: Re: [KCFusion] an easy one
Girish do you have a real world example of the proper usage of the group by clause? My book is real weak on this subject.
A.
----- Original Message -----
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, June 23, 2003 5:00 PM
Subject: RE: [KCFusion] an easy one
Looks like what he needs is
SELECT DISTINCT...
FROM table name
WHERE
GROUP BY
Girish
| [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/23/2003 05:06 PM | To: [EMAIL PROTECTED] cc: Subject: RE: [KCFusion] an easy one |
The results should have 1 of each student in a group, ex:
stu1/grpA
stu1/grpB
stu2/grpA
stu2/grpC
I believe the only way to get only 1 record for each student would be:
SELECT DISTINCT table1.studentID
FROM table1
Keep in mind that I'm no SQL guru...
Tyson
-----Original Message-----
From: Adaryl Wakefield [mailto:[EMAIL PROTECTED]
Sent: Monday, June 23, 2003 4:14 PM
To: [EMAIL PROTECTED]
Subject: Re: [KCFusion] an easy one
Yes
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, June 23, 2003 3:56 PM
Subject: RE: [KCFusion] an easy one
> Are the studentID and goalID unique when combined?
>
> -----Original Message-----
> From: Adaryl Wakefield [mailto:[EMAIL PROTECTED]
> Sent: Monday, June 23, 2003 3:55 PM
> To: [EMAIL PROTECTED]
> Subject: Re: [KCFusion] an easy one
>
>
> Tried that still got a funny record set. Here is a simplifaction of the
> statement
>
> SELECT DISTINCT table1.studentID, table2.goalID
> FROM table1, table2
> WHERE table1.studentID = table2.studentID
>
> I still keep getting multiple tuples for one studentID
> A.
> ----- Original Message -----
> From: <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Monday, June 23, 2003 3:31 PM
> Subject: RE: [KCFusion] an easy one
>
>
> > SELECT DISTINCT id
> >
> > -----Original Message-----
> > From: Adaryl Wakefield [mailto:[EMAIL PROTECTED]
> > Sent: Monday, June 23, 2003 3:33 PM
> > To: [EMAIL PROTECTED]
> > Subject: [KCFusion] an easy one
> >
> >
> > Its been so long since ive done this i forgot how. In an sql statement i
> > want only unique values of a field. I tried
> > SELECT Unique (id) as id
> >
> > but that did not work.
> > A.
> >
> >
> >
> >
> > ______________________________________________________________________
> > The KCFusion.org list and website is hosted by Humankind Systems, Inc.
> > List Archives........ http://www.mail-archive.com/[EMAIL PROTECTED]
> > Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED]
> > To Subscribe.................... mailto:[EMAIL PROTECTED]
> > To Unsubscribe................ mailto:[EMAIL PROTECTED]
> >
> >
> >
> > ______________________________________________________________________
> > The KCFusion.org list and website is hosted by Humankind Systems, Inc.
> > List Archives........ http://www.mail-archive.com/[EMAIL PROTECTED]
> > Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED]
> > To Subscribe.................... mailto:[EMAIL PROTECTED]
> > To Unsubscribe................ mailto:[EMAIL PROTECTED]
> >
> >
>
>
>
>
> ______________________________________________________________________
> The KCFusion.org list and website is hosted by Humankind Systems, Inc.
> List Archives........ http://www.mail-archive.com/[EMAIL PROTECTED]
> Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED]
> To Subscribe.................... mailto:[EMAIL PROTECTED]
> To Unsubscribe................ mailto:[EMAIL PROTECTED]
>
>
>
> ______________________________________________________________________
> The KCFusion.org list and website is hosted by Humankind Systems, Inc.
> List Archives........ http://www.mail-archive.com/[EMAIL PROTECTED]
> Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED]
> To Subscribe.................... mailto:[EMAIL PROTECTED]
> To Unsubscribe................ mailto:[EMAIL PROTECTED]
>
>
______________________________________________________________________
The KCFusion.org list and website is hosted by Humankind Systems, Inc.
List Archives........ http://www.mail-archive.com/[EMAIL PROTECTED]
Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED]
To Subscribe.................... mailto:[EMAIL PROTECTED]
To Unsubscribe................ mailto:[EMAIL PROTECTED]
______________________________________________________________________
The KCFusion.org list and website is hosted by Humankind Systems, Inc.
List Archives........ http://www.mail-archive.com/[EMAIL PROTECTED]
Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED]
To Subscribe.................... mailto:[EMAIL PROTECTED]
To Unsubscribe................ mailto:[EMAIL PROTECTED]
