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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Rodolfo J. Paiz | 2005-01-27 01:40:09 | Re: Formatting a month in query output |
Previous Message | Rodolfo J. Paiz | 2005-01-27 00:59:18 | Re: Formatting a month in query output |