Home » SQL & PL/SQL » SQL & PL/SQL » Max statement
Max statement [message #260604] Mon, 20 August 2007 07:49 Go to next message
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


Re: Max statement [message #260605 is a reply to message #260604] Mon, 20 August 2007 07:52 Go to previous messageGo to next message
joy_division
Messages: 4640
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.
Re: Max statement [message #260612 is a reply to message #260605] Mon, 20 August 2007 08:07 Go to previous messageGo to next message
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
Re: Max statement [message #260736 is a reply to message #260605] Mon, 20 August 2007 16:31 Go to previous messageGo to next message
kmck
Messages: 5
Registered: August 2007
Junior Member
Yes, I've tried that also, doesn't make any difference.

Thanks
Re: Max statement [message #260737 is a reply to message #260612] Mon, 20 August 2007 16:33 Go to previous messageGo to next message
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
Re: Max statement [message #260768 is a reply to message #260604] Mon, 20 August 2007 21:21 Go to previous messageGo to next message
flyboy
Messages: 1832
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
Re: Max statement [message #260957 is a reply to message #260768] Tue, 21 August 2007 06:22 Go to previous messageGo to next message
kmck
Messages: 5
Registered: August 2007
Junior Member
Brilliant, this seems to work....Thank you very much!!
Re: Max statement [message #261313 is a reply to message #260604] Wed, 22 August 2007 05:28 Go to previous messageGo to next message
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.
Re: Max statement [message #261351 is a reply to message #261313] Wed, 22 August 2007 06:36 Go to previous messageGo to next message
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.
Re: Max statement [message #261357 is a reply to message #261351] Wed, 22 August 2007 06:43 Go to previous messageGo to next message
Maaher
Messages: 7062
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 Go to previous messageGo to next message
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.
Re: Max statement [message #261365 is a reply to message #261361] Wed, 22 August 2007 07:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Max statement [message #261371 is a reply to message #260604] Wed, 22 August 2007 07:19 Go to previous messageGo to next message
flyboy
Messages: 1832
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.)
Re: Max statement [message #261373 is a reply to message #261361] Wed, 22 August 2007 07:21 Go to previous messageGo to next message
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
Re: Max statement [message #261379 is a reply to message #261373] Wed, 22 August 2007 07:26 Go to previous messageGo to next message
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
Re: Max statement [message #261380 is a reply to message #261379] Wed, 22 August 2007 07:36 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Analytics rocks, however when deciding between analytical vs aggregate functions, I would tend to use aggregate in this case.
Re: Max statement [message #261381 is a reply to message #261379] Wed, 22 August 2007 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Max statement [message #261410 is a reply to message #261381] Wed, 22 August 2007 09:22 Go to previous messageGo to next message
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.
Re: Max statement [message #261428 is a reply to message #261410] Wed, 22 August 2007 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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

Regards
Michel
Re: Max statement [message #261533 is a reply to message #261371] Wed, 22 August 2007 23:59 Go to previous messageGo to next message
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.
Re: Max statement [message #261561 is a reply to message #261533] Thu, 23 August 2007 01:07 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: showing the values just once for matching records
Next Topic: FORALL statement error while compiling
Goto Forum:
  


Current Time: Sat Dec 03 16:29:46 CST 2016

Total time taken to generate the page: 0.10476 seconds