• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

oracle query with max and group by clause

Enclosing a sample table script

Query to run

select max(day_of_week) as day_of_week, max(month) as month,order_by,
case when rep_id = 2  then goals end as Jen,
case when rep_id = 11  then  goals end as Sheri
from wkly_proposed_goal_improve
group by rep_id,goals,order_by
order by order_by

output is enclosed as spreadsheet.

Required or expected output is enclosed

I also wanted the query to give me data from the present month that we are running the report.  For now from JUL onwards.

I tried using where MONTH = to_char(sysdate,'MON') that is for jul
for going upto DEC  I could not get when I used >=
table-script.txt
query-output.xls
expected.xls
0
anumoses
Asked:
anumoses
  • 3
  • 3
1 Solution
 
sdstuberCommented:
SELECT MAX(day_of_week) AS day_of_week,
         MAX(month) AS month,
         order_by,
         MAX(CASE WHEN rep_id = 2 THEN goals END) AS jen,
         MAX(CASE WHEN rep_id = 11 THEN goals END) AS sheri
    FROM wkly_proposed_goal_improve
GROUP BY order_by
ORDER BY order_by
0
 
anumosesAuthor Commented:
Thanks for the solution

I also wanted the query to give me data from the present month that we are running the report.  For now from JUL onwards.
0
 
sdstuberCommented:
that's not what your expected results showed.

is month meaningful as a date criteria?

What about January of next year?  That's in the future, but January of last year is in the past.  How will comparing JAN to JUL help determine which is which?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
anumosesAuthor Commented:
I have the year in the table as of now it is for 2014. The user adds data to the table with year and corresponding goals

The below is the query that runs as a dbms_job every sunday night

 select distinct trunc(sysdate+1) as day_of_week,
                 YEAR,
                         a.REP_ID,
                         GOALS,
                         a. MONTH,
                         ORDER_BY,
                         GOALS*120/100 as booking_target,
                 booked_now, ( booked_now- (GOALS*120/100)) as booking_surplus_deficit
from  proposed_goal a , proposed_annual_goal b
 where a.rep_id = b.rep_id
   and a.month = b.month
   and booked_now is not null
 order by rep_id,order_by

So I will pull the report by adding the year.
0
 
sdstuberCommented:
SELECT MAX(day_of_week) AS day_of_week,
       MAX(month) AS month,
       order_by,
       MAX(CASE WHEN rep_id = 2 THEN goals END) AS jen,
       MAX(CASE WHEN rep_id = 11 THEN goals END) AS sheri
  FROM wkly_proposed_goal_improve
 WHERE year = EXTRACT(YEAR FROM SYSDATE)
   AND TO_DATE(year || month, 'yyyy-mm') >= TRUNC(SYSDATE, 'mm')
GROUP BY order_by
ORDER BY order_by
0
 
anumosesAuthor Commented:
thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now