string concatentation of multiple columns in select statement

[nospam]

I'm trying to do something like this in sqlalchemy:

select (score/10)*10 || '-' || (score/10)*10+9 as scorerange,
count(*)from scoresgroup by score/10 order by 1

which should give:
scorerange | count
------------+-------
0-9 | 11
10-19 | 14
20-29 | 3
30-39 | 2

Any ideas on how to do the string concatentation in the ranges?

[Michael Bayer]

the + operator resolves to the concatenation operator if used against an expression with a string type, or if using untyped elements concat() will get you there:

from sqlalchemy.sql import column

column('foo', String) + "bar"
column('foo').concat("bar")

your expression above appears to be evaluating a numeric against a string (so...this is MySQL ? :) ) you might consider calling cast(expr, String) on the numeric value before evaluating in a string context.

((score / 10) * 10).concat("-").concat((score / 10)*10) + 9).label('scorerange')

from sqlalchemy import cast, String
cast((score / 10) * 10, String).concat("-").concat(cast((score / 10)*10) + 9, String)).label('scorerange')

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