Where to put the Group By clause?

Question

Where to put the Group By clause?

mitchellk.williams

SSC Rookie

Points: 36
More actions
June 13, 2017 at 4:39 pm

#331074

I want to group the units by tenants, but when I put Group By tenant.scode after the where clause I get the following error:
Column 'property.scode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
select
p.scode
,tenant.scode
, memo.udate
, memo.sactivity
, memo.stext
, tenant.sfirstname
, tenant.slastname
, tenant.istatus
, tenant.saddr1
, tenant.saddr2
, tenant.srent
, tenant.dtmovein
, tenant.dtmoveout
, tenantaging.cage30
, tenantaging.cage60
, tenantaging.cage90
, tenantaging.cageover90
, tenant.dtleasefrom
FROM Memo
INNER JOIN tenant ON tenant.hproperty = memo.hprop
inner join property p on p.hmy = tenant.hproperty
INNER JOIN tenantaging ON tenantaging.hmy = tenant.hmyperson
WHERE sactivity = 'Delinquency' and memo.udate between '05/01/2017' and '06/13/2017'

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63594 More actions · Answer 1

general rule of thumb with aggregates / GROUP BY.

Columns in the SELECT clause must be either aggregates (Sum, Min, Max, Avg...etc) OR they must be in the GROUP BY clause. Anything else will cause the statement to fail.Re-reading your post, it sounds more like you want to present the data grouped by a column, and not aggregated. Is this in a report? If so, then you don't group in the SQL statement, but in the report.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

You don't need a group by on that query, as it has no aggregation.
Group By in SQL is used to do aggregations (sum, avg, count, min, max, etc) per <group by column>

Why are you considering a group By? What do you want it to do?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 3

mitchellk.williams - Tuesday, June 13, 2017 4:39 PM
I want to group the units by tenants, but when I put Group By tenant.scode after the where clause I get the following error:
Column 'property.scode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
select
p.scode
,tenant.scode
...
WHERE sactivity = 'Delinquency' and memo.udate between '05/01/2017' and '06/13/2017'

Do you mean you want to sort the data by tenant? in which case, you want an ORDER BY clause, which would go after your WHERE clause.

If you're wanting to make some distinction in a report based on those groups, then that's something that you should be doing in your report settings, rather than in SQL Server itself.

Thomas Rushton
blog: https://thelonedba.wordpress.com