GROUP BY single field 2

[pentesh]

Hi,

New to the forum but have taken lots of advice over the years. I am looking for help with a group by problem I am having. See example list of orders below.

---------------------------------------------------------------------------------------------------
control name street city country order_date order_amt
---------------------------------------------------------------------------------------------------
123456 John Doe 123 New Street Capital City Whereeverland 05/03/2011 100
123456 John Doe 123 New Street Capital City Whereeverland 06/04/2011 200
123456 John Doe 123 New Street Capital City Whereeverland 08/05/2011 100
123456 John Doe 123 New Street Capital City Whereeverland 09/06/2011 200
123456 John Doe 123 New Street Capital City Whereeverland 11/07/2011 100
123456 John Doe 789 Old Street Second City Whereeverland 12/08/2011 200
123456 John Doe 789 Old Street Second City Whereeverland 13/09/2011 100
123456 John Doe 789 Old Street Second City Whereeverland 15/10/2011 200
123456 John Doe 789 Old Street Second City Whereeverland 16/11/2011 100
---------------------------------------------------------------------------------------------------

If I do a regular GROUP BY I can get them to come out like so no problem.

---------------------------------------------------------------------------------------------------
control name street city country count(orders) sum(order_amt)
---------------------------------------------------------------------------------------------------
123456 John Doe 123 New Street Capital City Whereeverland 5 700
123456 John Doe 789 Old Street Second City Whereeverland 4 600
---------------------------------------------------------------------------------------------------

My question is whether it is possible to group them just based on one field (in this case the control number and then use the most frequent for each other field in this case the address with 5 orders against it rather than 4. I assume a subquery should help me here but I can't figure it.

---------------------------------------------------------------------------------------------------
control name street city country count(orders) sum(order_amt)
---------------------------------------------------------------------------------------------------
123456 John Doe 123 New Street Capital City Whereeverland 9 1300
---------------------------------------------------------------------------------------------------

If anyone can give me some pointers on the sql for this I would be very grateful.

Thanks
pentesh

 

[LKBrwnDBA]

... Etc...
My question is whether it is possible to group them just based on one field (in this case the control number and then use the most frequent for each other field in this case the address with 5 orders against it rather than 4. I assume a subquery should help me here but I can't figure it.
... Etc...
[/code]
So you don't care is if chooses the old address if there are more orders there?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[pentesh]

My primary purpose at the moment is to pick the most common address (so yes, in some cases this might be an old address)... Although now that you say it, I can see the most recent address being the next request!!

Unfortunately in my example, the 'old address' is also the most recent! oops!

 

[SantaMufasa]

Pentesh,

Yes, a subquery is helpful in your case, plus your need represents a good application of one of Oracle's Analytic Functions.

I duplicated your data, but I also added to your data set, another set of data representing a different control number and a different person and address, to confirm that the solution is solid. Here are the data:

select * from pentesh_order;

CONTROL NAME     STREET           CITY          COUNTRY       ORDER_DATE  ORDER_AMT
------- -------- ---------------- ------------- ------------- ---------- ----------
 123456 John Doe 123 New Street   Capital City  Whereeverland 05/03/2011        100
 123456 John Doe 123 New Street   Capital City  Whereeverland 06/04/2011        200
 123456 John Doe 123 New Street   Capital City  Whereeverland 08/05/2011        100
 123456 John Doe 123 New Street   Capital City  Whereeverland 09/06/2011        200
 123456 John Doe 123 New Street   Capital City  Whereeverland 11/07/2011        100
 123456 John Doe 789 Old Street   Second City   Whereeverland 12/08/2011        200
 123456 John Doe 789 Old Street   Second City   Whereeverland 13/09/2011        100
 123456 John Doe 789 Old Street   Second City   Whereeverland 15/10/2011        200
 123456 John Doe 789 Old Street   Second City   Whereeverland 16/11/2011        100
 987654 Mary Doe 987 Main Street  New York City USA           05/03/2011        500
 987654 Mary Doe 987 Main Street  New York City USA           06/04/2011         90
 987654 Mary Doe 987 Main Street  New York City USA           08/05/2011        500
 987654 Mary Doe 987 Main Street  New York City USA           09/06/2011         90
 987654 Mary Doe 987 Main Street  New York City USA           11/07/2011        500
 987654 Mary Doe 222 State Street Chicago       USA           12/08/2011        800
 987654 Mary Doe 222 State Street Chicago       USA           13/09/2011        900
 987654 Mary Doe 222 State Street Chicago       USA           15/10/2011        800
 987654 Mary Doe 222 State Street Chicago       USA           16/11/2011        900

18 rows selected.

Here is code that produces a solution you specified:

select control
       ,name
       ,street
       ,city
       ,country
       ,Cnt
       ,Total
  from (select control
              ,name
              ,street
              ,city
              ,country
              ,count(*) Cnt
              ,sum(order_amt) Total
              ,rank() over (partition by control
                                order by count(*) desc,name,street,city,country) rnk
          from pentesh_order
         group by control
                 ,name
                 ,street
                 ,city
                 ,country
       )
 where rnk = 1;

CONTROL NAME     STREET           CITY          COUNTRY              CNT      TOTAL
------- -------- ---------------- ------------- ------------- ---------- ----------
 123456 John Doe 123 New Street   Capital City  Whereeverland          5        700
 987654 Mary Doe 987 Main Street  New York City USA                    5       1680

2 rows selected.

The new element here, Oracle's Analytic Function, RANK (), produces the "first" row of each CONTROL group when the rows for each CONTROL are grouped by...

...the most frequent for each other field, in this case, the address...

You may wish to Google "Oracle Analytic Functions" to see an explanation of RANK () and other Analytic Functions.


If you have other questions, please post.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[pentesh]

Mufasa

I added a thanks star to the post but did not get to thank you properly for you help... so thanks!

This solution is 99% there, I wonder could you help me with the last piece.

In your solution you give the highest ranking by count and the sum for those records.

CONTROL NAME STREET CITY COUNTRY CNT TOTAL
------- -------- ---------------- ------------- ------------- ---------- ----------
123456 John Doe 123 New Street Capital City Whereeverland 5 700
987654 Mary Doe 987 Main Street New York City USA 5 1680

Is it possible to give the count and sum for all records by control & name, while adding the address components for the highest ranking by count (I hope I am making myself even slightly clear!)? As per below:

CONTROL NAME STREET CITY COUNTRY CNT TOTAL
------- -------- ---------------- ------------- ------------- ---------- ----------
123456 John Doe 123 New Street Capital City Whereeverland 9 1300
987654 Mary Doe 987 Main Street New York City USA 9 5080

This is obviously a bit of a manipulation of the data.

Thanks
pentesh

 

[SantaMufasa]

There are probably many solutions to your request. Following is one such solution. (I had to presume that the first address for each "CONTROL/NAME" combination is satisfactory.)

select  Addr.control
       ,Addr.name
       ,Addr.street
       ,Addr.city
       ,Addr.country
       ,Ord.Cnt
       ,Ord.Total
  from (select control
              ,name
              ,count(*) Cnt
              ,sum(order_amt) Total
          from pentesh_order
         group by control
                 ,name
       ) Ord
       ,
       (select control,name,street,city,country,
               rank () over (partition by control,name
                   order by control,name,street,city,country) rnk
          from pentesh_order
         group by control,name,street,city,country
       ) Addr
 where Ord.control = Addr.control
   and Ord.Name = Addr.Name
   and rnk = 1;

CONTROL NAME     STREET           CITY          COUNTRY              CNT      TOTAL
------- -------- ---------------- ------------- ------------- ---------- ----------
 123456 John Doe 123 New Street   Capital City  Whereeverland          9       1300
 987654 Mary Doe 222 State Street Chicago       USA                    9       5080

Let us know if this is satisfactory or if you have follow-up questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.