On Fri, Oct 2, 2015 at 5:03 PM, Tom Lane wrote:
> "David G. Johnston" writes:
> > This...on 9.3
> > SELECT array_agg(
> > distinct case when v % 2 = 0 then 'odd' else 'even' end
> > order by case when v % 2 = 0 then 1 else 2 end
> > )
> > FROM (VALUES (1), (2), (3)) val (v)
>
> The error message
"David G. Johnston" writes:
> This...on 9.3
> SELECT array_agg(
> distinct case when v % 2 = 0 then 'odd' else 'even' end
> order by case when v % 2 = 0 then 1 else 2 end
> )
> FROM (VALUES (1), (2), (3)) val (v)
The error message seems pretty clear to me:
ERROR: in an aggregate with DISTINCT,
This...on 9.3
SELECT array_agg(
distinct case when v % 2 = 0 then 'odd' else 'even' end
order by case when v % 2 = 0 then 1 else 2 end
)
FROM (VALUES (1), (2), (3)) val (v)
I'm not particularly irked at this though I was hoping to fix a somewhat
complex query of mine by simply adding a "DISTINCT"
[EMAIL PROTECTED] wrote:
> Quoting Richard Huxton <[EMAIL PROTECTED]>:
>> But I'm guessing that's not what you mean. Can you show what output
>> you'd like to have?
>
> effort sum
> 1 245
> 2 463
> etc.
>
> Each value for effort has a different statistical meaning as in the case
> state
Garry Saddington wrote:
> Is there a way to make the following statement return the total of all
> effort. At the moment it gives a list of sum vs case.
>
>
> select sum(effort),
> CASE WHEN effortandattainment.effort=5 THEN -3
> WHEN effortandattainment.effort=4 THEN -2
> WHEN effortandattainm
Is there a way to make the following statement return the total of all effort.
At the moment it gives a list of sum vs case.
select sum(effort),
CASE WHEN effortandattainment.effort=5 THEN -3
WHEN effortandattainment.effort=4 THEN -2
WHEN effortandattainment.effort=3 THEN 1
WHEN effortandattain
Richard Huxton wrote:
Ah, but this just includes the time of the last message, not its data.
Oops, I read the OP's question as "date and time", rather than "data
and time". Nevermind. :)
- John D. Burger
MITRE
---(end of broadcast)---
T
John D. Burger wrote:
On Jun 21, 2007, at 09:22, Richard Huxton wrote:
Naz Gassiep wrote:
Hi,
If I have a table with users and a table with messages, is it
possible to have a query that returns user.* as well as one extra column
with the number of messages they have posted and the data an
On Jun 21, 2007, at 09:22, Richard Huxton wrote:
Naz Gassiep wrote:
Hi,
If I have a table with users and a table with messages, is it
possible to have a query that returns user.* as well as one extra
column
with the number of messages they have posted and the data and time of
the last m
Naz Gassiep wrote:
Hi,
If I have a table with users and a table with messages, is it
possible to have a query that returns user.* as well as one extra column
with the number of messages they have posted and the data and time of
the last message? At the moment I am using a subquery to do this,
Hi,
If I have a table with users and a table with messages, is it
possible to have a query that returns user.* as well as one extra column
with the number of messages they have posted and the data and time of
the last message? At the moment I am using a subquery to do this,
however it seems sub
On Nov 7, 2005, at 17:47 , David Fetter wrote:
On Mon, Nov 07, 2005 at 05:12:05PM +0900, Michael Glaesmann wrote:
I'm trying to concatenate strings in variable orders using a custom
aggregate. However, I'm having a difficult time figuring out the
SQL I need to use to accomplish this.
How ab
Michael Glaesemann <[EMAIL PROTECTED]> writes:
> On Nov 7, 2005, at 23:24 , Tom Lane wrote:
>> Strictly speaking, you need this:
>> select bar_id, array_accum(foo_value) from
>> (select * from ordered_foo order by bar_id, foo_pos) as ss
>> group by bar_id order by bar_id;
>> ie, sort
On Nov 7, 2005, at 23:24 , Tom Lane wrote:
Strictly speaking, you need this:
select bar_id, array_accum(foo_value) from
(select * from ordered_foo order by bar_id, foo_pos) as ss
group by bar_id order by bar_id;
ie, sort the subselect by the grouping key of the outer q
Joe Conway <[EMAIL PROTECTED]> writes:
> Michael Glaesemann wrote:
>> I'm trying to concatenate strings in variable orders using a custom
>> aggregate.
> Just use a subselect -- you're looking for this, correct?
> regression=# select bar_id, array_accum(foo_value) from (select * from
> ordere
On Monday, November 07, 2005 12:12 AM
Michael Glaesemann wrote:
>
> select bar_id, array_accum(foo_value)
> from ordered_foo
> group by bar_id
> order by bar_id;
> bar_id | array_accum
> +-
>1 | {delta,alpha,charlie,bravo}
>2 | {C,B,A,D}
On Nov 7, 2005, at 17:40 , Roger Hand wrote:
On Monday, November 07, 2005 12:12 AM
Michael Glaesemann wrote:
select bar_id, array_accum(foo_value)
from ordered_foo
group by bar_id
order by bar_id;
bar_id | array_accum
+-
1 | {delta,alpha,char
Michael Glaesemann wrote:
I'm trying to concatenate strings in variable orders using a custom
aggregate. However, I'm having a difficult time figuring out the SQL I
need to use to accomplish this. Here's a test case that shows the error
I'm getting.
select bar_id, array_accum(foo_value)
f
On Mon, Nov 07, 2005 at 05:12:05PM +0900, Michael Glaesmann wrote:
> I'm trying to concatenate strings in variable orders using a custom
> aggregate. However, I'm having a difficult time figuring out the
> SQL I need to use to accomplish this.
How about using the ARRAY() constructor as below?
>
I'm trying to concatenate strings in variable orders using a custom
aggregate. However, I'm having a difficult time figuring out the SQL
I need to use to accomplish this. Here's a test case that shows the
error I'm getting.
select version();
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Frans) wrote:
> This yields the message: 'Aggregates not allowed in WHERE clause'.
>
> Can somebody help me here
What it says is right; SQL does not permit using aggregates in the
WHERE clause.
You need to look at the HAVING claus
On Thu, Dec 16, 2004 at 12:02:34PM +0700, Frans wrote:
> I try to use : select name, sum(quantity) from info where
> sum(quantity)>20 group by name;
> This yields the message: 'Aggregates not allowed in WHERE clause'.
For aggregates use HAVING, not WHERE:
SELECT name, SUM(quantity)
FROM info
GRO
Frans <[EMAIL PROTECTED]> writes:
> I try to use : select name, sum(quantity) from info where
> sum(quantity)>20 group by name;
> This yields the message: 'Aggregates not allowed in WHERE clause'.
> Can somebody help me here
See
http://www.postgresql.org/docs/7.4/static/tutorial-agg.html
part
Hi All,
I have a table (table info) with 2 column, column 'name' and column 'quantity'.
name | quantity
--
a | 5
b | 3
a | 3
c | 4
b | 6
If I want to sum the sum of all entry in table.
name | quantity
a
On Mon, Sep 27, 2004 at 07:04:50PM -0400, Esteban Kemp wrote:
Esteban,
> I'm building a Large DataMart with a big table and I want to improve
> the performace using aggregates I mean a set of table that store
> some specific aggregacion of the main table, like materialized
> views, I know
This is the Problem:
I'm building a Large DataMart with a big
table and I want to improve the performace using aggregates I mean a set of
table that store some specific aggregacion of the main table, like materialized
views, I know that this feature is not implemented yet in postgres, And
Does anyone know of any plans to support aggregates of aggregates in the
future? It would be great to be able to select with an aggregate function
from another select statement with another aggregate function. I have
tried putting the result of an aggregate function in a view and then
performing a
27 matches
Mail list logo
