Proper Use of the Group By Clause

al4629740
al4629740 used Ask the Experts™
on
The way I'm grouping these variable at the end of my query is wrong.  What am I doing wrong here so that I can properly group by ActivityDate, HourTimeFrom, HourTimeTo, ActivityID?

select Agency, Program,ActivityID, RegID, ActivityDate, Hours, ltrim(right(convert(varchar(25), HourTimeFrom, 100), 7)) as 'Time From',
        ltrim(right(convert(varchar(25), HourTimeTo, 100), 7)) as 'Time To', SubGroup, ParticipantHour, VolunteerHour, Fiscal, EntryTime, Classification, Objectives 
        from tblOrgHours Where AgencyID = '74' And ActivityDate >= '7/1/2018' And 
        ActivityDate <= '6/30/2019' group by ActivityDate, HourTimeFrom, HourTimeTo, ActivityID

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
Simply put, everything you do not state in GROUP BY needs to be in an aggregate like SUM. MIN. MAX, COUNT in the select field list. That is, you would have write:
select min(Agency), min(Program),ActivityID, min(RegID), ActivityDate, min(Hours), ltrim(right(convert(varchar(25), HourTimeFrom, 100), 7)) as 'Time From',
        ltrim(right(convert(varchar(25), HourTimeTo, 100), 7)) as 'Time To', min(SubGroup), min(ParticipantHour), min(VolunteerHour), min(Fiscal), min(EntryTime), min(Classification), min(Objectives)
        from tblOrgHours Where AgencyID = '74' And ActivityDate >= '7/1/2018' And 
        ActivityDate <= '6/30/2019'
group by ActivityDate, HourTimeFrom, HourTimeTo, ActivityID

Open in new window

The reason is that there cannot be different values for non-grouped columns - what should get displayed then?

Author

Commented:
Thanks.  I know Min and Max mean finding the lowest and highest value, but why is that necessary if it doesn't matter in this query?  Just a question to learn.

Author

Commented:
Also the query you provided returns the error:

Msg 8117, Level 16, State 1, Line 2
Operand data type bit is invalid for min operator.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
I can't help much with that error, because I don't know your table structure and data types. Obviously one of the fields is a BIT type.

The issue with "doesn't matter" is that SQL does not have that setting. You need to tell it exactly what it should do.
Using MIN is an arbitrary decision, this will of course always show the lowest value found in the group.
On the other hand, if information doesn't matter, why do you want to see it? A GROUP BY reduces the information shown, and you need to tell what should happen with information you want to see but is not part of the GROUP BY.

There are ways to only show the first row with a particular order for each group, or all rows with a group identifier (e.g. running number within the same group). It all depends on what you really want to get.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial