OraFAQ Forum: SQL & PL/SQL » Max statement

Home » SQL & PL/SQL » SQL & PL/SQL » Max statement

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

Max statement [message #260604]

Mon, 20 August 2007 07:49

kmck
Messages: 5
Registered: August 2007

Junior Member

Hi

Hoping for some help here please....

I have the following example data set, from a table called "cubes":

SKU QTY CUBE DATE LOC
HBN 60 19 16/08/2007 1:05:37 p.m. B02
HBN 32 10 16/08/2007 1:05:37 p.m. B04
HBN 58 18 16/08/2007 5:06:13 p.m. B02
HBN 32 10 16/08/2007 5:06:13 p.m. B04
HBN 57 18 16/08/2007 11:05:40 p.m. B02
HBN 32 10 16/08/2007 11:05:40 p.m. B04
KGU 50 54 16/08/2007 1:05:37 p.m. B01
KGU 9 9 16/08/2007 1:05:37 p.m. B04
KGU 45 51 16/08/2007 5:06:13 p.m. B01
KGU 7 8 16/08/2007 5:06:13 p.m. B04
KGU 60 52 16/08/2007 11:05:40 p.m. B01
KGU 9 9 16/08/2007 11:05:40 p.m. B04
THP 22 24 16/08/2007 1:05:37 p.m. B01
THP 41 44 16/08/2007 1:05:37 p.m. B02
THP 17 20 16/08/2007 5:06:13 p.m. B01
THP 41 44 16/08/2007 5:06:13 p.m. B02
THP 17 20 16/08/2007 11:05:40 p.m. B01
THP 41 44 16/08/2007 11:05:40 p.m. B02

What I want to do is extract the maximum summed qty value for each sku where the date / time value is the same.
In the above data set what I would want returned is:

16/08/2007 HBN 92
16/08/2007 KGU 69
16/08/2007 THP 63

The only thing I can get to run is the following:

select max(trunc(reportdate)),max(sku),max(sum(cubes.qty))  
from cubes
where trunc(reportdate) = '16/08/2007'
group by reportdate,sku

The problem is that with a max around all the select components, it returns only the record set for the highest sku value alphabetically - 16/08/2007 THP 63

If I don't have a max around the first 2 select components (reportdate and sku) then I get:
ORA-00937: not a single-group group function

Any ideas?!

Am running this via TOAD on Oracle 8i

Thanks

Report message to a moderator

Re: Max statement [message #260605 is a reply to message #260604]

Mon, 20 August 2007 07:52

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

kmck wrote on Mon, 20 August 2007 08:49

[code]
where trunc(reportdate) = '16/08/2007'

You cannot compare a DATE column to a character string. Please remedy the situation before anything else by using the built-in TO_DATE function.

Report message to a moderator

Re: Max statement [message #260612 is a reply to message #260605]

Mon, 20 August 2007 08:07

sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata

Senior Member

You get this error because you are trying to use AGREEGRATE functions twice in a single group by operation so either you have to use same AGREEGRATE functions for each element of the select statement otherwise you don't have the scope of it.

Cheers
Sanka

Report message to a moderator

Re: Max statement [message #260736 is a reply to message #260605]

Mon, 20 August 2007 16:31

kmck
Messages: 5
Registered: August 2007

Junior Member

Yes, I've tried that also, doesn't make any difference.

Thanks

Report message to a moderator

Re: Max statement [message #260737 is a reply to message #260612]

Mon, 20 August 2007 16:33

kmck
Messages: 5
Registered: August 2007

Junior Member

OK, thanks - how would I use the same AGREEGRATE functions for each element of the select statement in the sample code I posted?

Thanks

Report message to a moderator

Re: Max statement [message #260768 is a reply to message #260604]

Mon, 20 August 2007 21:21

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Quote:

OK, thanks - how would I use the same AGREEGRATE functions for each element of the select statement in the sample code I posted?

It is the way you constructed the first statement. Seems it is not the good way as you are not satisfied with its results.

You shall aggregate only once in one select statement.
Moreover, you do not aggregate on the same columns.
Use two queries, something like:

-- counting max qty sum for the same (trunc(reportdate), sku) by grouping
SELECT trunc(reportdate) reportdate, sku, max(qty)
FROM (
  -- counting qty sums for the same (reportdate, sku) by grouping
  SELECT reportdate, sku, sum(qty) qty
  FROM cubes
  WHERE reportdate >= to_date( '16/08/2007', 'dd/mm/yyyy' )
    AND reportdate < to_date( '17/08/2007', 'dd/mm/yyyy' )
  GROUP BY reportdate, sku
)
GROUP BY trunc(reportdate), sku

Report message to a moderator

Re: Max statement [message #260957 is a reply to message #260768]

Tue, 21 August 2007 06:22

kmck
Messages: 5
Registered: August 2007

Junior Member

Brilliant, this seems to work....Thank you very much!!

Report message to a moderator

Re: Max statement [message #261313 is a reply to message #260604]

Wed, 22 August 2007 05:28

darshanmeel
Messages: 44
Registered: June 2007
Location: India

Member

What I want to do is extract the maximum summed qty value for each sku where the date / time value is the same.

If this is what you want then

Please use like this

select sku,reportdate,sum(qty)
from cubes
group by sku,reportdate

This is the simple answer for waht you hae asked above in bold.Please let me know if thats wht you require or you need some other thing.

Report message to a moderator

Re: Max statement [message #261351 is a reply to message #261313]

Wed, 22 August 2007 06:36

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

If you read the OPs question, you'll see that he gives an example to clarify what he is asking.
I don't think your solution quite gives the results he's looking for.

Report message to a moderator

Re: Max statement [message #261357 is a reply to message #261351]

Wed, 22 August 2007 06:43

Maaher
Messages: 7065
Registered: December 2001

Senior Member

darshanmeel,

I am sorry but I inadvertently deleted your post. I was trying to format it. Could you post again and use some kind of formatting?

MHE

[Updated on: Wed, 22 August 2007 06:44]

Report message to a moderator

Re: Max statement [message #261361 is a reply to message #261357]

Wed, 22 August 2007 06:53

darshanmeel
Messages: 44
Registered: June 2007
Location: India

Member

Given below id the input data which was posted by user.

SKU QTY CUBE DATE LOC
HBN 60 19 16/08/2007 1:05:37 p.m. B02
HBN 32 10 16/08/2007 1:05:37 p.m. B04
HBN 58 18 16/08/2007 5:06:13 p.m. B02
HBN 32 10 16/08/2007 5:06:13 p.m. B04
HBN 57 18 16/08/2007 11:05:40 p.m. B02
HBN 32 10 16/08/2007 11:05:40 p.m. B04
KGU 50 54 16/08/2007 1:05:37 p.m. B01
KGU 9 9 16/08/2007 1:05:37 p.m. B04
KGU 45 51 16/08/2007 5:06:13 p.m. B01
KGU 7 8 16/08/2007 5:06:13 p.m. B04
KGU 60 52 16/08/2007 11:05:40 p.m. B01
KGU 9 9 16/08/2007 11:05:40 p.m. B04
THP 22 24 16/08/2007 1:05:37 p.m. B01
THP 41 44 16/08/2007 1:05:37 p.m. B02
THP 17 20 16/08/2007 5:06:13 p.m. B01
THP 41 44 16/08/2007 5:06:13 p.m. B02
THP 17 20 16/08/2007 11:05:40 p.m. B01
THP 41 44 16/08/2007 11:05:40 p.m. B02

Now the output form the user which he has posted.

What I want to do is extract the maximum summed qty value for each sku where the date / time value is the same.
In the above data set what I would want returned is:

16/08/2007 HBN 92
16/08/2007 KGU 69
16/08/2007 THP 63

The examination of the output shows that the data was grouped by SKU and then the date on 16/08/2007 1:05:37 P.M.i.e. for HBN values at time 16/08/2007 1:05:37 P.M are given below:

HBN 60 19 16/08/2007 1:05:37 p.m. B02
HBN 32 10 16/08/2007 1:05:37 p.m. B04
KGU 50 54 16/08/2007 1:05:37 p.m. B01
KGU 9 9 16/08/2007 1:05:37 p.m. B04
THP 22 24 16/08/2007 1:05:37 p.m. B01
THP 41 44 16/08/2007 1:05:37 p.m. B02

60+32=92 HBN
41+22=63 for THP
50+9=59 for KGU it looks different as in output it was 69.
Also in wording he has mentioned the same thing.This requirement simply looks like a simple group by sku and reportdate.

But after that whole forum looks like goes soemwhere else.
Michel I tried to format it and think i have done it atleats 50% properly.Will do better next time.

Report message to a moderator

Re: Max statement [message #261365 is a reply to message #261361]

Wed, 22 August 2007 07:05

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Michel I tried to format it and think i have done it atleats 50% properly.Will do better next time.

Good.

Regards
Michel

Report message to a moderator

Re: Max statement [message #261371 is a reply to message #260604]

Wed, 22 August 2007 07:19

flyboy
Messages: 1903
Registered: November 2006

Senior Member

kmck,
thanks for feedback, i am glad it helped.

darshanmeel,
you seem to be good in giving solutions, however you should improve your reading abilities. please, sit down calmly and re-read this forum again.
maybe you will discover other answer followed with feedback from original poster...

just for your information,
What I want to do is extract the maximum summed qty value for each sku where the date / time value is the same.

HBN 60 19 16/08/2007 1:05:37 p.m. B02
HBN 32 10 16/08/2007 1:05:37 p.m. B04
..
KGU 60 52 16/08/2007 11:05:40 p.m. B01
KGU 9 9 16/08/2007 11:05:40 p.m. B04
..
THP 22 24 16/08/2007 1:05:37 p.m. B01
THP 41 44 16/08/2007 1:05:37 p.m. B02

results into

16/08/2007 HBN 92
16/08/2007 KGU 69
16/08/2007 THP 63

(as 69 is bigger than 59 at 1:05:37 p.m.)

Report message to a moderator

Re: Max statement [message #261373 is a reply to message #261361]

Wed, 22 August 2007 07:21

kmck
Messages: 5
Registered: August 2007

Junior Member

Hi Guys

The solution posted above from flyboy has worked for me, thanks for your efforts though.

Note that the KGU max total should have been 69 as per the 11.05 report date, not 59 as at 1.05.
Just a coincidence that in teh sample data set I gave the other 2 skus had their maximums both at the 1.05 report date.

In the actual data sets which are captured hourly, teh maximums for each sku could be at any time.

Regards
kmck

Report message to a moderator

Re: Max statement [message #261379 is a reply to message #261373]

Wed, 22 August 2007 07:26

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Analytic anybody?

drop table tsts;

create table tsts ( sku  varchar2(3), qty number, dte date);

insert into tsts values ('HBN', 60,  to_date('16/08/2007 1:05:37 ','dd/mm/yyyy hh:mi:ss'));  
insert into tsts values ('HBN', 32,  to_date('16/08/2007 1:05:37 ','dd/mm/yyyy hh:mi:ss'));  
insert into tsts values ('HBN', 58,  to_date('16/08/2007 5:06:13 ','dd/mm/yyyy hh:mi:ss'));  
insert into tsts values ('HBN', 32,  to_date('16/08/2007 5:06:13 ','dd/mm/yyyy hh:mi:ss'));  
insert into tsts values ('HBN', 57,  to_date('16/08/2007 11:05:40 ','dd/mm/yyyy hh:mi:ss')); 
insert into tsts values ('HBN', 32,  to_date('16/08/2007 11:05:40 ','dd/mm/yyyy hh:mi:ss')); 
insert into tsts values ('KGU', 50,  to_date('16/08/2007 1:05:37 ','dd/mm/yyyy hh:mi:ss'));  
insert into tsts values ('KGU', 9 ,  to_date('16/08/2007 1:05:37 ','dd/mm/yyyy hh:mi:ss'));  
insert into tsts values ('KGU', 45,  to_date('16/08/2007 5:06:13 ','dd/mm/yyyy hh:mi:ss'));  
insert into tsts values ('KGU', 7 ,  to_date('16/08/2007 5:06:13 ','dd/mm/yyyy hh:mi:ss'));  
insert into tsts values ('KGU', 60,  to_date('16/08/2007 11:05:40 ','dd/mm/yyyy hh:mi:ss')); 
insert into tsts values ('KGU', 9 ,  to_date('16/08/2007 11:05:40 ','dd/mm/yyyy hh:mi:ss')); 
insert into tsts values ('THP', 22,  to_date('16/08/2007 1:05:37 ','dd/mm/yyyy hh:mi:ss'));  
insert into tsts values ('THP', 41,  to_date('16/08/2007 1:05:37 ','dd/mm/yyyy hh:mi:ss'));  
insert into tsts values ('THP', 17,  to_date('16/08/2007 5:06:13 ','dd/mm/yyyy hh:mi:ss'));  
insert into tsts values ('THP', 41,  to_date('16/08/2007 5:06:13 ','dd/mm/yyyy hh:mi:ss'));  
insert into tsts values ('THP', 17,  to_date('16/08/2007 11:05:40 ','dd/mm/yyyy hh:mi:ss')); 
insert into tsts values ('THP', 41,  to_date('16/08/2007 11:05:40 ','dd/mm/yyyy hh:mi:ss')); 

alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss';

select distinct 
       sku
      ,trunc(dte)  dte
      ,first_value(qty_sum) over (partition by sku,trunc(dte) order by qty_sum desc)  qty_sum
from (
select sku,sum(qty) qty_sum,dte
from   tsts
group by sku,dte);

SKU	DTE	                QTY_SUM
THP	16-08-2007 00:00:00	63
KGU	16-08-2007 00:00:00	69
HBN	16-08-2007 00:00:00	92

Report message to a moderator

Re: Max statement [message #261380 is a reply to message #261379]

Wed, 22 August 2007 07:36

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Analytics rocks, however when deciding between analytical vs aggregate functions, I would tend to use aggregate in this case.

Report message to a moderator

Re: Max statement [message #261381 is a reply to message #261379]

Wed, 22 August 2007 07:43

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

JRowbottom,

I didn't read the topic so I can't answer but when I see DISTINCT + analytic function, I know (by experience, having made this countless times) that this not the good way.

The analytic equivalence for your query is:

SQL> select sku, trunc(dte)  dte, qty_sum
  2  from ( select sku, sum(qty) qty_sum, dte,
  3         row_number () over (partition by sku,trunc(dte) order by sum(qty) desc) rn
  4         from   tsts
  5         group by sku,dte )
  6  where rn=1
  7  /
SKU DTE                    QTY_SUM
--- ------------------- ----------
HBN 16-08-2007 00:00:00         92
KGU 16-08-2007 00:00:00         69
THP 16-08-2007 00:00:00         63

3 rows selected.

Once again, I don't know if this fit the requirements as I didn't read the topic.

Thanks for posting a test case.
Regards
Michel

Report message to a moderator

Re: Max statement [message #261410 is a reply to message #261381]

Wed, 22 August 2007 09:22

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

There are a lot of opinions about the best solution, and not enough numbers.
Let's have a benchmark.

declare
  v_iter  pls_integer := 100000;
  v_ret1  varchar2(10);
  v_ret2  date;
  v_ret3  number;
  
  v_time  number;
  v_test  pls_integer;
begin
  v_time := dbms_utility.get_time;
  v_test := 1;
  for i in 1..v_iter loop
    for rec in (select distinct 
       sku
      ,trunc(dte)  dte
      ,first_value(qty_sum) over (partition by sku,trunc(dte) order by qty_sum desc)  qty_sum
       from (select sku,sum(qty) qty_sum,dte
             from   tsts
             group by sku,dte)) loop
  
      v_ret1 := rec.sku;
      v_ret2 := rec.dte;
      v_ret3 := rec.qty_sum;
    end loop;
  end loop;
  
  dbms_output.put_line('Test '||v_test||' - '||to_char(dbms_utility.get_time-v_time));
  
  v_time := dbms_utility.get_time;
  v_test := 2;
  for i in 1..v_iter loop
    for rec in (select sku, trunc(dte)  dte, qty_sum
                from ( select sku, sum(qty) qty_sum, dte,
                       row_number () over (partition by sku,trunc(dte) order by sum(qty) desc) rn
                       from   tsts
                       group by sku,dte )
                where rn=1) loop
  
      v_ret1 := rec.sku;
      v_ret2 := rec.dte;
      v_ret3 := rec.qty_sum;
    end loop;
  end loop;
  
  dbms_output.put_line('Test '||v_test||' - '||to_char(dbms_utility.get_time-v_time));  
  
  v_time := dbms_utility.get_time;
  v_test := 3;
  for i in 1..v_iter loop
    for rec in (SELECT trunc(dte) dte, sku, max(qty) qty_sum
                FROM ( SELECT dte, sku, sum(qty) qty
                       FROM tsts
                       WHERE dte >= to_date( '16/08/2007', 'dd/mm/yyyy' )
                       AND   dte < to_date( '17/08/2007', 'dd/mm/yyyy' )
                       GROUP BY dte, sku)
                       GROUP BY trunc(dte), sku) loop
  
      v_ret1 := rec.sku;
      v_ret2 := rec.dte;
      v_ret3 := rec.qty_sum;
    end loop;
  end loop;
  
  dbms_output.put_line('Test '||v_test||' - '||to_char(dbms_utility.get_time-v_time));    
end;
/

Test 1 - 6030
Test 2 - 4096
Test 3 - 5612

As we can see, Michel was corect - his solution is substantially faster.

Report message to a moderator

Re: Max statement [message #261428 is a reply to message #261410]

Wed, 22 August 2007 10:16

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I will not bet this is right for any number of rows.
Analytic often works well when there are few rows as it can work in memory.
When number of rows grows, the alternative option often beat it.

So as always, it depends. Wink

Wink

Regards
Michel

Report message to a moderator

Re: Max statement [message #261533 is a reply to message #261371]

Wed, 22 August 2007 23:59

darshanmeel
Messages: 44
Registered: June 2007
Location: India

Member

Thanks Flyboy on Correcting me.But still there is one more select statement is requred to get the max of sum.The same solution has been suggested earlier and becnhmark has been given.But i was wondering why there was where clause to specify the start and end date.But the same could beachieved by group by only.
I didnt use where clause. This is kind of general query now.This will give details for all details.If you need the details of a particular date(16/08/2007) Then u can use where clause. The where cleause could be used in outer query.

I have done the beanchmarking usinh

set timing on and i have found the following observaton.Before seting timing on i have queried the table so that table was in memory.

Here is what i have gotten...

SQL> set timing on
SQL> select distinct 
  2  sku
  3  ,trunc(dte) dte
  4  ,first_value(qty_sum) over (partition by sku,trunc(dte) order by qty_sum desc) qty_sum
  5  from (select sku,sum(qty) qty_sum,dte
  6  from tsts
  7  group by sku,dte)
  8  /

SKU DTE            QTY_SUM
--- ----------- ----------
HBN 16-AUG-2007         92
KGU 16-AUG-2007         69
THP 16-AUG-2007         63

Elapsed: 00:00:00.63
SQL> select sku, trunc(dte) dte, qty_sum
  2  from ( select sku, sum(qty) qty_sum, dte,
  3  row_number () over (partition by sku,trunc(dte) order by sum(qty) desc) rn
  4  from tsts
  5  group by sku,dte )
  6  where rn=1
  7  /

SKU DTE            QTY_SUM
--- ----------- ----------
HBN 16-AUG-2007         92
KGU 16-AUG-2007         69
THP 16-AUG-2007         63

Elapsed: 00:00:00.47
SQL> SELECT trunc(dte) dte, sku, max(qty) qty_sum
  2  FROM ( SELECT dte, sku, sum(qty) qty
  3  FROM tsts
  4  GROUP BY dte, sku)
  5  GROUP BY trunc(dte), sku
  6  /

DTE         SKU    QTY_SUM
----------- --- ----------
16-AUG-2007 HBN         92
16-AUG-2007 KGU         69
16-AUG-2007 THP         63

Elapsed: 00:00:00.47
SQL> select distinct 
  2  sku
  3  ,trunc(dte) dte
  4  ,first_value(qty_sum) over (partition by sku,trunc(dte) order by qty_sum desc) qty_sum
  5  from (select sku,sum(qty) qty_sum,dte
  6  from tsts
  7  group by sku,dte)
  8  /

SKU DTE            QTY_SUM
--- ----------- ----------
HBN 16-AUG-2007         92
KGU 16-AUG-2007         69
THP 16-AUG-2007         63

Elapsed: 00:00:00.62
SQL> select sku, trunc(dte) dte, qty_sum
  2  from ( select sku, sum(qty) qty_sum, dte,
  3  row_number () over (partition by sku,trunc(dte) order by sum(qty) desc) rn
  4  from tsts
  5  group by sku,dte )
  6  where rn=1
  7  /

SKU DTE            QTY_SUM
--- ----------- ----------
HBN 16-AUG-2007         92
KGU 16-AUG-2007         69
THP 16-AUG-2007         63

Elapsed: 00:00:00.47
SQL> SELECT trunc(dte) dte, sku, max(qty) qty_sum
  2  FROM ( SELECT dte, sku, sum(qty) qty
  3  FROM tsts
  4  GROUP BY dte, sku)
  5  GROUP BY trunc(dte), sku
  6  /

DTE         SKU    QTY_SUM
----------- --- ----------
16-AUG-2007 HBN         92
16-AUG-2007 KGU         69
16-AUG-2007 THP         63

Elapsed: 00:00:00.47

Thanks a lot and any input on this would be great for everyone who is involbved with this thread.

Report message to a moderator

Re: Max statement [message #261561 is a reply to message #261533]

Thu, 23 August 2007 01:07

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As I said benchmark with a couple of rows is relatively meaningless.
In addition, when you want to compare use the way JRowbottom shows, this prevent from SQL*Plus inaccuracy, client workload, network workload...

Regards
Michel

Report message to a moderator

Previous Topic:	showing the values just once for matching records
Next Topic:	FORALL statement error while compiling

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 20:56:08 CST 2024