getting maximum entry from a sum()

Поиск
Список
Период
Сортировка
От Jan Danielsson
Тема getting maximum entry from a sum()
Дата
Msg-id 45265C82.6040501@gmail.com
обсуждение исходный текст
Ответы Re: getting maximum entry from a sum()  ("A. Kretschmer" <andreas.kretschmer@schollglas.com>)
Re: getting maximum entry from a sum()  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-novice
Hi,

   I have a table, which essentially is:

transactions (
   id serial,
   amount numeric(8,2),
   dt date
)

   I use this to keep track of my expenses. I want to take out the
maximum expense for a date/week/month/year. But let's just focus on a
date. I start out with this query:

economy=> select dt,sum(amount) as asum from transactions group by dt
order by asum;

   As expected, this will yield a list of all dates I have wasted my
money, and how much I wasted for those dates. Now let's day I only
wanted the maximum amount I spent and what date that was.

   Obviously I could "order by asum" and "limit 1", but this would only
get a single date. What if I want *all* dates which have the same
maximum asum?

   Essentially, I want:

select dt,sum(amount) as asum where asum=(select max(asum) ...) group by dt

   But I can't seem to understand how to formulate such a query.. I've
been trying off and on for a few days now, and I'm only getting more and
more frustrated by it.

   Any hints?

--
Kind Regards,
Jan Danielsson
Te audire non possum. Musa sapientum fixa est in aure.


Вложения

В списке pgsql-novice по дате отправления:

Предыдущее
От: "Tomeh, Husam"
Дата:
Сообщение: Re: Interface of the R-tree in order to work with
Следующее
От: "A. Kretschmer"
Дата:
Сообщение: Re: getting maximum entry from a sum()