Forum Stats

  • 3,876,212 Users
  • 2,267,082 Discussions
  • 7,912,473 Comments

Discussions

Help Using the PIVOT Function

FriendlyTooo
FriendlyTooo Member Posts: 133 Blue Ribbon
edited Mar 28, 2017 7:54PM in SQL & PL/SQL

Hi Folks:

Background:

We have (currently) 10 rain gauges.  I am using the following SQL code to group the AVG of the gauges per month for the current year:

  SELECT TO_CHAR (RAIN_DATE, 'MM/YYYY'), AVG (PRECIPITATION_AMOUNT)
    FROM WEATHER.PRECIPITATION
GROUP BY TO_CHAR (RAIN_DATE, 'YYYY'), TO_CHAR (RAIN_DATE, 'MM/YYYY')
  HAVING TO_CHAR (RAIN_DATE, 'YYYY') = TO_CHAR (SYSDATE, 'YYYY')
ORDER BY TO_DATE (TO_CHAR (RAIN_DATE, 'MM/YYYY'), 'MM/YYYY') DESC;

An example of the current output would be:

04/2017    6.465

03/2017    .496363636

So for example March had an average of almost .5 inches and (fake data) April almost 6.5 inches.

What I need to be able to do for a report is to make the month(s) into columns and the  AVG amount of rain into the row  (so there will always only be one row of data that contains the avg amount of rain. Each month a new average is added.

Any help on how to use the pivot function would be greatly appreciated.

Thanks,

Matthew

Tagged:
John ThortonFrank Kulash
«1

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Mar 24, 2017 10:59PM
  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Mar 25, 2017 12:21AM

    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.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,794 Red Diamond
    edited Mar 25, 2017 9:13AM

    Hi,

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

    Explain, using specific examples, how you get those results from that data.

    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

    See the forum FAQ:

    Do you know how to get the results you want in 1 column, with a separate row for each month?  (Actually, that might be a better format for these results.  Make sure whoever needs this output really prefers 1 row, with a separate column for each month, before going any farther.)

    Assuming you really do want 1 row, then start by getting the results in separate rows for each month, using plain old GROUP BY and AVG.

    Include a column that indicates the month (just the month, not including the year).  Either

    TO_CHAR (rain_date, 'MM')        or

    EXTRACT (MONTH FROM rain_date)   will work.

    Once you have a result set like this, you can use the SELECT ... PIVOT feature (it's a feature, not a function) to produce a separate column for each month, as shown in the link John posted in reply #1.  Since you already computed the average, the aggregate function used in the PIVOT can be MIN or MAX

    If you have trouble, post your code.

    John Thorton
  • mathguy
    mathguy Member Posts: 10,920 Black Diamond
    edited Mar 25, 2017 10:14AM

    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
  • FriendlyTooo
    FriendlyTooo Member Posts: 133 Blue Ribbon
    edited Mar 25, 2017 11:28AM

    Thanks for trying to help folks.  I tried reading the links from John but for whatever reason my brain is stuck.

    I'll try to make it easier:  I created a view  with just two columns:

    SELECT

    RAIN_MONTH, AMOUNT

    FROM WEATHER.PRECIP_CURRENTYEAR_AVG

    Currently it displays like this:

    04/2017    6.465

    03/2017    0.2935

    The goal is to get it to display the month as columns so it ends up (at the end of the year with 12 columns) like this:

    03/2017        04/2017    05/2017

    6.465            0.2935      4.40

    I tried stuff like this:

    SELECT * FROM

    (

    SELECT

    RAIN_MONTH, AMOUNT

    FROM WEATHER.PRECIP_CURRENTYEAR_AVG

    ) PIVOT

    ( MONTH

    FOR AVG(AMOUNT)

    IN (01/2017, 02/2017, 03/2017, 04/2017)

    )

    ORDER BY ASC

  • FriendlyTooo
    FriendlyTooo Member Posts: 133 Blue Ribbon
    edited Mar 25, 2017 11:29AM

    Thanks for trying to help folks.   I tried reading the links from John but for whatever reason my brain is stuck.

    I'll try to make it easier:   I created a view  with just two columns:

    SELECT

    RAIN_MONTH, AMOUNT

    FROM WEATHER.PRECIP_CURRENTYEAR_AVG

    Currently it displays like this:

    04/2017    6.465

    03/2017    0.2935

    The goal is to get it to display the month as columns so it ends up (at the end of the year with 12 columns) like this:

    03/2017         04/2017     05/2017

    6.465             0.2935       4.40

    I tried stuff like this  but I have something wrong:

    SELECT * FROM

    (

    SELECT

    RAIN_MONTH, AMOUNT

    FROM WEATHER.PRECIP_CURRENTYEAR_AVG

    ) PIVOT

    ( MONTH

    FOR AVG(AMOUNT)

    IN (01/2017, 02/2017, 03/2017, 04/2017)

    )

    ORDER BY ASC

    Thanks again for help

    Matthew

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,794 Red Diamond
    edited Mar 25, 2017 12:34PM

    Hi,

    What's wrong with the solution Mathguy posted in reply #4?  Point out where it's getting the wrong results, and explain what the correct results are in those places.  If necessary, post additional sample data.

    If you want to use the SELECT ... PIVOT feature, here's one way to implement the plan from reply #3:

    WITH    rain_month    AS(    SELECT    TO_CHAR (rain_date, 'MON')  AS rain_month    ,         AVG (precipitation_amount)  AS avg_amount    FROM      precipitation    WHERE     rain_date  >= TRUNC (SYSDATE, 'YEAR')  -- current year only    GROUP BY  TO_CHAR (rain_date, 'MON'))SELECT    *FROM      rain_monthPIVOT     (    MIN (avg_amount)          FOR  rain_month  IN ( 'JAN'  AS jan                              , 'FEB'  AS feb                              , 'MAR'  AS mar                         --   ...                              , 'DEC'  AS dec                              )          );

    Output (using the sample data from reply #4):

         JAN      FEB      MAR      DEC-------- -------- -------- -------- 2.00085  1.66105
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,794 Red Diamond
    edited Mar 25, 2017 1:24PM

    Hi, Matthew,

    FriendlyTooo wrote:Thanks for trying to help folks. I tried reading the links from John but for whatever reason my brain is stuck.I'll try to make it easier: I created a view with just two columns:SELECT RAIN_MONTH, AMOUNTFROM WEATHER.PRECIP_CURRENTYEAR_AVG

    Post the CREATE VIEW statement.

    How do you plan to use the view, aside from this one query?

    I tried stuff like this but I have something wrong:

    Would you ever tell a car mechanic just "There's something wrong with my car", without giving any details?

    Try to help the people who want to help you.  Give some clue about exactly what is wrong.  Did it raise an error?  Post the complete error message. Did it get the wrong results?  Did it get the right results, but in  the wrong order?  Was it something else?

    At the very least, post enough (e.g., CREATE TABLE, INSERT and CREATE VIEW statements) so that people can re-create the problem themselves.

    SELECT * FROM(SELECTRAIN_MONTH, AMOUNTFROM WEATHER.PRECIP_CURRENTYEAR_AVG) PIVOT( MONTH

    Review the syntax of SELECT ... PIVOT.  Compare your query to similar queries that work.

    The first thing after 'PIVOT (" is always an aggregate function.

    Do you have a column called MONTH, or is the column called RAIN_MONTH?

    FOR AVG(AMOUNT)

    You can't use functions immediately the FOR keyword.

    IN (01/2017, 02/2017, 03/2017, 04/2017)

    What are the values that identify the output columns?  Is

    01/2017     (which is just another way of writing

    1 / 2017    or approximately

    .0004958    ) one of those values?

    )ORDER BY ASC

    You need an expression (such as a column name) right before the ASC keyword.

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Mar 26, 2017 5:36PM
    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.

    And as you can see Frank already has to ask you again and again to POST your DDL statements of the tables/views you are using in your example.

    That's the WHOLE point of the FAQ (Frequently Asked Questions) and the post is a guide on HOW TO ASK A QUESTION on these forums.  Very simple Friendly Tooo.

    So do what Frank asked

    Post the CREATE VIEW statement.

    How do you plan to use the view, aside from this one query?"

    Apologies as I didn't see that the link John posted was not the "how to ask a question".  Disregard this post.

  • mathguy
    mathguy Member Posts: 10,920 Black Diamond
    edited Mar 25, 2017 9:27PM
    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

This discussion has been closed.