Help Using the PIVOT Function

Please READ John's reply and read that LINK.

Then provided DDL statements of your objects (tables) and DML statements of your data (INSERT statements).

Also search this forum on PIVOT questions.  You'll find tons.

The PIVOT operator works better when the grouping is by values in one or more columns. If you need to group by expressions (such as the month extracted from the RAIN_DATE) you may be better off pivoting "manually" - using the pivoting technique that was used for a long time, before the introduction of the PIVOT operator.

In your problem description you mentioned the ten gauges, but you didn't say what role they play. You can average over all gauges and all dates in a month, OR you can average separately for each gauge, and only over all dates in a month (getting a table with ten rows, one for each gauge, and 12 columns, one for each month). In the solution below I adopt the first position: average over all gauges and all dates in a month. If you need averages by gauge AND month, then add GAUGE to the SELECT clause and add GROUP BY GAUGE at the end of the query.

Column names must be known at parse time (unless you are willing to do more complicated things); column names can and should be controlled through your reporting system anyway. I use hard-coded column names JAN, FEB, MAR...

I added just a little bit of test data in a WITH clause. Obviously, that is NOT part of the solution; the query begins after the WITH clause.

On first reading, one might think that the WHERE clause is redundant, since rows where the date is not "this year" don't contribute to the computations anyway. Actually the WHERE clause plays an important role: it rejects rows from "other years" immediately, instead of processing each such "useless" row in 12 CASE expressions.   EDIT: Actually this comment doesn't quite apply anymore. Initially I had written the CASE expressions in terms of comparing RAIN_DATE to specific month and year, and I added the WHERE clause for the reason I just explained. (The explanation is incomplete: the main benefit of the WHERE clause is that unneeded rows aren't even read from disk in the first place, if there's an index on RAIN_DATE - even regardless of them being or not being processed in CASE expressions.) But after I added the WHERE clause, I realized I can now simply use EXTRACT (MONTH FROM...) - but now the WHERE clause is in fact needed to ensure the year is the right one. I am not deleting this paragraph since I think the idea is correct, and it may be helpful in other cases.

Also, the condition should be on RAIN_DATE and not on TRUNC(RAIN_DATE) or TO_CHAR(RAIN_DATE) of any kind, so that an index on RAIN_DATE, if it exists (as it should!) can be used. Even avoiding unnecessary function calls is important for performance, but the ability to use an index is enormously more so.

with precipitation (gauge, rain_date, precipitation_amount) as (
      select 3, date '2016-12-03', 8.4228 from dual union all
      select 8, date '2016-12-04', 3.2019 from dual union all
      select 1, date '2017-01-01', 3.8002 from dual union all
      select 2, date '2017-01-01', 3.1002 from dual union all
      select 3, date '2017-01-03', 0      from dual union all
      select 3, date '2017-01-06', 1.103  from dual union all
      select 1, date '2017-02-20', 2.3901 from dual union all
      select 4, date '2017-02-23', 0.932  from dual
    )
select avg(case extract(month from rain_date) when 1 then precipitation_amount end) as jan,
       avg(case extract(month from rain_date) when 2 then precipitation_amount end) as feb,
       avg(case extract(month from rain_date) when 3 then precipitation_amount end) as mar
from   precipitation
where  rain_date >= trunc(sysdate, 'y') and rain_date < add_months(trunc(sysdate, 'y'), 12)
;JAN      FEB      MAR
-------  -------  -------
2.00085  1.66105

jaramill wrote:FriendlyTooo wrote:Thanks for trying to help folks. I tried reading the links from John but for whatever reason my brain is stuck.Nonsense. The link is to post that describes HOW you should ask ANY question on these forums? It wasn't specific to your question but for all questions.[...]

Not really. Please read Reply 1 again (to which the OP was referring). Then edit your Reply 9 as appropriate.    Cheers,   mathguy