Group by with Named Parameter with Spring's NamedParamterJdbcTemplate

I'm using PostgreSQL and Spring 3.1 NamedParameterJdbcTemplate.

If I have some SQL which uses a named parameter in the Select & Group By clauses, Postgres will return this as bad grammar. Running the query with the parameters manually replaced will work correctly.

Assume there is a table called 'table' with 2 columns "id" and "number"

This SQL:

SELECT some_function(id, :param), avg(number) FROM table GROUP BY some_function(id, :param)

Becomes (as logged by the DB):

SELECT some_function(id, $1), avg(number) FROM table GROUP BY some_function(id, $2)

I suspect that the query planner looks at this query and it doesn't know that $1==$2, so it gives the error "column 'id' must appear in the GROUP BY clause or be used in an aggregate function."

Is there are way to get around this using Spring? Is there some way to get it so the query becomes:

SELECT some_function(id, $1), avg(number) FROM table GROUP BY some_function(id, $1) instead? The query planner would be okay with this I suspect.

Answers


You can write:

SELECT some_function(id, $1), avg(number) FROM table GROUP BY 1;

It will group by the first column (some_function(id, $1)) of the SELECT result.

Another way is to write something like this:

SELECT grp, avg(number)
FROM (SELECT some_function(id, $1) as grp, number FROM table)
GROUP BY grp;

Need Your Help

Every time I divide I get 0.0

java android

I am trying to make an app that splits the bill between people but whenever i try to divide the two numbers in the code it always gives me 0.0 . Why is this?

Breakpoint in MACRO - IAR Assembly MSP430

msp430 iar

I am trying to measure the clocks each instruction takes when executed in my assembly program but IAR won't allow me to set breakpoints inside a MACRO. I can set a breakpoint before the MACRO call ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.