Skip site navigation (1) Skip section navigation (2)

Re: Formatting a month in query output

From: Alexander Borkowski <alexander(dot)borkowski(at)abri(dot)une(dot)edu(dot)au>
To: "Rodolfo J(dot) Paiz" <rpaiz(at)simpaticus(dot)com>
Cc: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>,pgsqlnovice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Formatting a month in query output
Date: 2005-01-27 01:31:44
Message-ID: 41F84480.5070001@abri.une.edu.au (view raw or flat)
Thread:
Lists: pgsql-novice
Rodolfo J. Paiz wrote:
> Sometimes two flights are made on the same day (there and back), so 124
> flights were made on a total of 87 unique dates. This query, which I
> assume does a primary group by date and a secondary group by month,
> provides 87 rows which have the month column formatted correctly but
> correspond to the 87 unique dates available. There are, of course,
> multiple rows for each month.

Sorry, I totally missed the crucial point there. How about

select date_trunc('month', date) as sort_month, to_char(date,'Mon YYYY') 
as month, count(num) as num, sum(hrs_total) as hours from flights group 
by sort_month, month order by sort_month asc;

> I also attempted this:
> 
> flightlog=> select to_char(date, 'Mon YYYY') as month, count(date) as
> nnn, sum(hrs_total) as hours from flights group by month order by date
> asc;
> ERROR:  column "flights.date" must appear in the GROUP BY clause or be
> used in an aggregate function
> 
> Notice "count(date) as nnn", so that now I *am* using it in an aggregate
> function. I tried "order by date" and also "order by "nnn". No joy. But
> I don't understand why...

Yes, you are using it in an aggregate function but not referring to the 
result in the "order by"-clause when using "order by date". I don't know 
why "order by nnn" would fail though (apart from not doing what you 
want). I realise now that my comment about the aggregate function 
probably does not make too much sense in this context, I just tried to 
explain what the error message was all about.

Cheers,

Alex

In response to

Responses

pgsql-novice by date

Next:From: Rodolfo J. PaizDate: 2005-01-27 01:40:09
Subject: Re: Formatting a month in query output
Previous:From: Rodolfo J. PaizDate: 2005-01-27 00:59:18
Subject: Re: Formatting a month in query output

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group