Proper Use of the Group By Clause

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

al4629740Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
al4629740Author 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.
0
al4629740Author 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.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.