Subscribe to this thread
Home - General / All posts - Using an expression in Group By is failing
HeyGL7 post(s)
#11-Nov-22 08:07

Folks, advice please as to why this SQL is failing:

SELECT StringStart(d.[eventDate],4), count(1) as Records 

FROM <MySQLServerTable> as d

GROUP BY StringStart(d.[eventDate],4)

It does work (but does not group by) if I use the following, but that's not conforming SQL:

SELECT StringStart(d.[eventDate],4), count(1) as Records 

FROM <MySQLServerTable> as d

GROUP BY d.[eventDate]

Works fine in SQL Server (using LEFT() in place of Stringstart())

(It's gonna be some simple error that I can't see)

Thanks

Greg

tjhb
10,017 post(s)
#11-Nov-22 09:27

I wish I could remember the thread where adamw explained this so clearly.

Yes, there is an important difference introduced in Manifold 9, due to new evaluation order.

If I can find the thread I will post it, but in summary I believe you ~must do this (not tested).

--SQL9

SELECT [date], COUNT(1) as [records] 

FROM 

  (

  SELECT StringStart([eventDate], 4) AS [date]

  FROM <MySQLServerTable>

  )

GROUP BY [date];

BTW when something "is failing" it really helps to if you can say what happens--what the error message is, or whether your house sinks on its piles by a few inches, for example.

tjhb
10,017 post(s)
#11-Nov-22 10:08

SELECT and GROUP BY are no longer evaluated together (so to speak). Each must stand alone.

In SQL9 it is not enough to write matching expressions in the SELECT list and in the GROUP list (which to my mind always seemed to rely on a strange fiction).

So the rule now becomes: that only the source fields used for grouping may appear (unaggregated) in the SELECT list. An expression using a source field is a new entity--so while it can be used for grouping, it can't also be listed in SELECT. As you have found.

Nesting is the solution, when required.

(I still think I haven't put this clearly. Sorry about that.)

tjhb
10,017 post(s)
#11-Nov-22 10:29

Here's another way of putting it.

SELECT "doesn't read" the GROUP BY clause.

It only evaluates what is passed to it.

So if GROUP BY creates a new value, such as the result of StringStart([eventDate], 4), then what SELECT can see is the resulting value.

It no longer sees the source field that the value was made from--and therefore can't list that field, nor use it in its own expression.

HeyGL7 post(s)
#11-Nov-22 10:34

tjhb, you remind me that a subquery select has worked in the (long distant) past; bit of a fiddle, but can live with it. Thanks for your assistance - obliged.

Greg

tjhb
10,017 post(s)
#11-Nov-22 10:41

Yes a bit of a fiddle.

But what we get from the independence of the SELECT and GROUP nodes is... speed.

HeyGL7 post(s)
#11-Nov-22 10:32

re BTW - yes of course, apologies.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.