Subscribe to this thread
Home - General / All posts - Radian SQL: grop by question
apo
171 post(s)
#17-Aug-17 09:35

Hi all,

Using manifold for a long time I always found interesting to have the possibility to inject operators in the group by clause as for time lag queries for example (see below). A nice tool to easily generate histograms

SELECT floor([time]/5)*5 as [timelag],

count([id]as [Nbr]

FROM [Table]

GROUP BY floor([time]/5)*5

Trying to replicate such an approach in Radian make me face an error message that the [time] field should be either part of GROUP BY or part of aggregate. So my question do I have to do it differently in Radian?

Thank's to any advice

Best

adamw


10,447 post(s)
#17-Aug-17 11:51

Use a nested query:

--SQL9

SELECT [timelag], count([id]as [Nbr] FROM (

  SELECT floor([time]/5)*5 as [timelag][id] FROM [Table]

GROUP BY [timelag];

(I didn't test the modified query on example data, but even if it does not work out of the box, hopefully you see the concept.)

apo
171 post(s)
#18-Aug-17 08:25

Thx Adam, that was my in the meantime strategy but I just wanted not to miss something. By the way the M8 capability to handle the operators in group by is elegant

a.

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