OraFAQ Forum: SQL & PL/SQL » Return individuals most regular areas

Home » SQL & PL/SQL » SQL & PL/SQL » Return individuals most regular areas (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Return individuals most regular areas [message #628697]

Thu, 27 November 2014 01:59

asr77
Messages: 5
Registered: November 2014

Junior Member

Hi all,

I have a table with 3 columns - name, area, hours. There are multiple entries per name with multiple areas against hours. I need an SQL query to total the hours per name per area and return each name against there most regular area based on the hours.

I have searched the board and Google and have found similar queries but can't quite get them to work the way I need them to.

Any help would be greatly appreciated.

Thanks.
Andrew.

Report message to a moderator

Re: Return individuals most regular areas [message #628702 is a reply to message #628697]

Thu, 27 November 2014 03:30

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I'm not sure I understood what you mean (test case would help a lot), so - here's what I managed to do: first, select total hours per name & area (that would be SUMHRS). Then select name & area which have MAX total hours (if that's what "most regular area based on the hours" means).

with sumhrs as
  (select name, area, sum(hours) sum_hours
   from table
   group by name, area
  )
select name, area, max(sum_hours) max_sum_hours
from sumhrs
group by name, area

Report message to a moderator

Re: Return individuals most regular areas [message #628703 is a reply to message #628697]

Thu, 27 November 2014 03:42

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

Quote:

but can't quite get them to work the way I need them to

Please explain with example, what is the way you need the output. Also provide create table and insert record scripts for your dataset.

Report message to a moderator

Re: Return individuals most regular areas [message #628705 is a reply to message #628703]

Thu, 27 November 2014 04:21

asr77
Messages: 5
Registered: November 2014

Junior Member

Thanks for the replies, below is an example table and expected results -

TABLE (Name, Area, Hours)

Andrew, A, 2.00
Richard, A, 2.00
Andrew, B, 3.00
Richard, A, 3.00
Robert, C, 5.00

RESULTS (Name, Area)

Andrew, B
Richard, A
Robert, C

Andrew spent most time in area B
Richard spent most time in area A
Robert spent most time in area C

Thanks.
Andrew.

Report message to a moderator

Re: Return individuals most regular areas [message #628706 is a reply to message #628705]

Thu, 27 November 2014 04:24

asr77
Messages: 5
Registered: November 2014

Junior Member

Quote:

Littlefoot - I don't think my Oracle interface supports the WITH statement, I just keep getting errors..?

Ignore this, just me being silly, again! (It's going to be a long day...)

[Updated on: Thu, 27 November 2014 04:29]

Report message to a moderator

Re: Return individuals most regular areas [message #628707 is a reply to message #628706]

Thu, 27 November 2014 04:29

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What is your interface?

Quote:

Also provide create table and insert record scripts for your dataset.

What should be the result if several areas match the criteria (for instance if you also Andrew, D, 3.00)?
Here's one possible query:

select name, area
from (select name, area, 
             rank() over (partition by name order by hours desc) rk
      from mytable)
where rk = 1
/

Report message to a moderator

Re: Return individuals most regular areas [message #628716 is a reply to message #628707]

Thu, 27 November 2014 06:03

asr77
Messages: 5
Registered: November 2014

Junior Member

Michel Cadot wrote on Thu, 27 November 2014 10:29

What is your interface?

Quote:
Also provide create table and insert record scripts for your dataset.

What should be the result if several areas match the criteria (for instance if you also Andrew, D, 3.00)?
Here's one possible query:
select name, area
from (select name, area, 
             rank() over (partition by name order by hours desc) rk
      from mytable)
where rk = 1
/

Thanks for the reply Michel.

The results of these query show each person against multiple areas. Please correct me if I'm wrong, but it looks like it's sorting the hours per person and showing the highest value. Although an individual row may have the largest number of hours for an area, the total of that area may put it below another area. i.e. -

Andrew, A, 2.00
Richard, A, 2.00
Andrew, B, 3.00
Richard, A, 3.00
Robert, C, 5.00
Richard, B, 4.00

I've added another row to show Richard's area should be A (based on total) rather than B (based on the individual row).

EDIT: The multiple areas caused by there being lots of areas with the same individual/untotaled values(?)

Let me know if this doesn't make sense and I'll try to do another example.

Thanks.
Andrew.

[Updated on: Thu, 27 November 2014 06:10]

Report message to a moderator

Re: Return individuals most regular areas [message #628720 is a reply to message #628716]

Thu, 27 November 2014 06:34

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Next time no test case (create table + insert data) - no help. But since it's Thanksgiving:

SQL> select  *
  2    from  tbl
  3  /

NAME    A      HOURS
------- - ----------
Andrew  A          2
Richard A          2
Andrew  B          3
Richard A          3
Robert  C          5
Richard B          4

6 rows selected.

SQL> with t as (
  2             select  name,
  3                     area,
  4                     rank() over(partition by name order by sum(hours) desc) rnk
  5               from  tbl
  6               group by name,
  7                        area
  8            )
  9  select  name,
 10          area
 11    from  t
 12    where rnk = 1
 13  /

NAME    A
------- -
Andrew  B
Richard A
Robert  C

SQL>

SY.

Report message to a moderator

Re: Return individuals most regular areas [message #628722 is a reply to message #628720]

Thu, 27 November 2014 06:57

asr77
Messages: 5
Registered: November 2014

Junior Member

Solomon Yakobson wrote on Thu, 27 November 2014 12:34

Next time no test case (create table + insert data) - no help. But since it's Thanksgiving:

SQL> select  *
  2    from  tbl
  3  /

NAME    A      HOURS
------- - ----------
Andrew  A          2
Richard A          2
Andrew  B          3
Richard A          3
Robert  C          5
Richard B          4

6 rows selected.

SQL> with t as (
  2             select  name,
  3                     area,
  4                     rank() over(partition by name order by sum(hours) desc) rnk
  5               from  tbl
  6               group by name,
  7                        area
  8            )
  9  select  name,
 10          area
 11    from  t
 12    where rnk = 1
 13  /

NAME    A
------- -
Andrew  B
Richard A
Robert  C

SQL>

SY.

That's the one! Thank you!! one last thing, is there any way to remove double results where someone has exactly the same total for two areas? just pick the first one sequentially?

Thanks.
Andrew.

PS. Happy Thanksgiving Smile

Report message to a moderator

Re: Return individuals most regular areas [message #628723 is a reply to message #628722]

Thu, 27 November 2014 07:05

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Just change RANK() to ROW_NUMBER():

with t as (
           select  name,
                   area,
                   row_number() over(partition by name order by sum(hours) desc) rn
             from  tbl
             group by name,
                      area
          )
select  name,
        area
  from  t
  where rn = 1
/

SY.

Report message to a moderator

Previous Topic:	number to character conversion
Next Topic:	Unable to get the procedure compiled -PLS-00306:Wrong number or types of arguments

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 01:31:55 CDT 2024