Re: Formatting a month in query output

On Thu, 2005-01-27 at 10:36 +1100, Alexander Borkowski wrote:
> Try this:
>
> select to_char(date,'Mon YYYY') as month, count(num) as num,
> sum(hrs_total) as hours from flights group by date, month order by date asc;
>

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.

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...

---

By now I've figured out that I could just do to_char(date, 'YYYY-MM')
and then use a case statement in my PHP code to reformat the date, but
that's no fun. Now I want to figure out just what the hell it wants me
to type so I get the results I want.

Call me stubborn...

--
Rodolfo J. Paiz <rpaiz@simpaticus.com>