Query & Group

[take.it.easy]

I have a datetime and a temperature column in a google spreadsheet.
I'm trying to query to find the high temperatures by date and display
the time of day that the high temperature occurred, then visualize the
result.

A=datetime
B=temperature

My query is:

SELECT toDate(A), max(B) group by toDate(A)

...that works fine. But if I try to query with:

SELECT toDate(A), A, max(B) group by toDate(A)

...to include the A (so I can display the time that the high temp.
occurred), I get the following error:

"Error in query: Invalid query Column [A] should be added to GROUP BY,
removed from SELECT, or aggregated in SELECT."

So how do I include the time?

[Viz Kid]

Hi.

I'm not sure I understand what the problem is.

In the first query you suggested here, you get in return two columns - the date (A) and the maximum temperature. Why do you need to add yet another A to the query?

  Viz Kid

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To post to this group, send email to google-visua...@googlegroups.com.
To unsubscribe from this group, send email to google-visualizati...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-visualization-api?hl=en.

[take.it.easy]

Hi -

As I tried to explain, I was trying to show the TIME that the high
temperature occurred, thus the reason for the A column. Other query
languages (MySQL and MSSql) would let you include column A without an
aggregate function.

Thanks...

On Feb 11, 1:22 am, Viz Kid <viz...@google.com> wrote:
> Hi.
>
> I'm not sure I understand what the problem is.
> In the first query you suggested here, you get in return two columns - the
> date (A) and the maximum temperature. Why do you need to add yet another A
> to the query?
>
>   Viz Kid
>

> On Wed, Feb 10, 2010 at 7:30 PM, take.it.easy <wgcampb...@gmail.com> wrote:
> > I have a datetime and a temperature column in a google spreadsheet.
> > I'm trying to query to find the high temperatures by date and display
> > the time of day that the high temperature occurred, then visualize the
> > result.
>
> > A=datetime
> > B=temperature
>
> > My query is:
>
> > SELECT toDate(A), max(B) group by toDate(A)
>
> > ...that works fine.  But if I try to query with:
>
> > SELECT toDate(A), A, max(B) group by toDate(A)
>
> > ...to include the A (so I can display the time that the high temp.
> > occurred), I get the following error:
>
> > "Error in query: Invalid query Column [A] should be added to GROUP BY,
> > removed from SELECT, or aggregated in SELECT."
>
> > So how do I include the time?
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "Google Visualization API" group.
> > To post to this group, send email to
> > google-visua...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > google-visualizati...@googlegroups.com<google-visualization-api%2Bunsu...@googlegroups.com>

[Gabe]

I have a similar problem.  I am tracking items(column B) and each item has multiple locations with a date(A), lat(E), long(F), and description(G) of that location.  I would like to query the latest location for each item.

I can query the latest date with  'SELECT B, MAX(A) GROUP BY B', but I don't get the location information with that.  When I try 'SELECT B,E,F MAX(A) GROUP BY B' I get an error telling me I need to either remove E and F from Select, or add them to the Group by.  But when I add them to the Group By, I get multiple locations for each item, so it defeats my query.

Any suggestions on how to retain all my location columns, but only show the most recent location for each item? 

 

[asgallant]

When performing a SQL group (or pseudo-SQL as is the case here) everything you select must either be a part of the group by clause or have a grouping function applied.  At a guess, I'd say you want the values of E and F corresponding to MAX(A), right?  The API's pseudo-SQL doesn't have anything that allows you to do this.  You could query your data source without the group and then perform the group in js using the API's built in Group method.  You'd have to write a custom aggregator function to get the results you want, but that's the only way to do it.

[Gabe]

Thanks.  

I'll stick with doing the grouping in js.  I was hoping I could just punch in a query and be off, but I should still be able to make it work with a little effort.

[IR John]

If you database supports the RANK() over syntax

 

select * from (

select b,e,f,a,

 rank() over B order by date(a) desc  as therank

from [YourTablenameHere]

) as innersql where therank=1

 

If your database doesn't have a rank function you could try something like

 

select t1.A,t1.B,t1.E,t1.F from sometable as t1 inner join (

select a,max (B) as b from sometable )  as t2 on t1.A=t2.A and t1.B = t1.B