Re: Formatting a month in query output

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