Home » SQL & PL/SQL » SQL & PL/SQL » sql query problem
sql query problem [message #324850] Wed, 04 June 2008 06:04 Go to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
How can I get the total cost of 10 - 9122DS (item) most recently purchased from the following data

Quote:

purchase_date item qty cost
------------------- --------------- ------- -------------
23-May-08 , 9122DS , 1 , 5700
25-May-08 , 9122DS , 4 , 5700
25-May-08 , 9122DS , 5 , 5500
25-May-08 , 9122DS , 2 , 5700
27-May-08 , 9122DS , 1 , 5900
27-May-08 , 9122DS , 5 , 5900




Query should return

Item .Qty .Cost
9122DS ,10 ,57800
Re: sql query problem [message #324853 is a reply to message #324850] Wed, 04 June 2008 06:15 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Please make your question clear!

Re: sql query problem [message #324856 is a reply to message #324850] Wed, 04 June 2008 06:26 Go to previous messageGo to next message
kecd_deepak
Messages: 52
Registered: December 2007
Member
Hello,
As i understand your problem you can do...

-> Order your data according to purchase_date in DESC
-> Then use SUM with your condition

regards
Deepak
Re: sql query problem [message #324858 is a reply to message #324853] Wed, 04 June 2008 06:28 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
from bottom to top in the above data 10 9122DS item
it means

purchased_date item cost total_cost qty*cost

27-May-08 ,9122DS ,5 5900 29500
27-May-08 ,9122DS ,1 5900 5900
25-May-08 ,9122DS ,2 5700 11400

next record in the data has 5 in qty column
so only 2 items would be added and total will become 10
25-May-08 ,9122DS ,2 5500 11000

hence total cost will be 57800

Re: sql query problem [message #324859 is a reply to message #324850] Wed, 04 June 2008 06:29 Go to previous messageGo to next message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
Which 10 items do you need to find the total?

Are you looking for something like:
SELECT item, SUM(qty) qty, SUM(cost) cost
FROM table_name
GROUP BY item;

[Updated on: Wed, 04 June 2008 06:30]

Report message to a moderator

Re: sql query problem [message #324860 is a reply to message #324850] Wed, 04 June 2008 06:30 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
so what would be the query?
Re: sql query problem [message #324861 is a reply to message #324860] Wed, 04 June 2008 06:31 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
No 10 items which are recently purchased
Re: sql query problem [message #324865 is a reply to message #324850] Wed, 04 June 2008 06:37 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
can anyone help me?
Re: sql query problem [message #324867 is a reply to message #324865] Wed, 04 June 2008 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements.
Also post the result you want with these data.


Regards
Michel
Re: sql query problem [message #324872 is a reply to message #324850] Wed, 04 June 2008 07:02 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member

Create table purchase_data ( purchase_date date, item varchar2(15), qty number(6), cost number(12,2))

Insert into purchase_data values (’23-may-2008’,’9122DS’,1,5700);
Insert into purchase_data values (’25-may-2008’,’9122DS’,4,5700);
Insert into purchase_data values (’25-may-2008’,’9122DS’,5,5500);
Insert into purchase_data values (’25-may-2008’,’9122DS’,2,5700);
Insert into purchase_data values (’27-may-2008’,’9122DS’,1,5900);
Insert into purchase_data values (’27-may-2008’,’9122DS’,5,5900);



desired result:
10 9122DS (item) most recently purchased and their cost.

To get the desired data should arrange in the DESC order on purchae_date column,
now go down in the ordered data to find the 10 items and their cost

result would be
item Qty cost
9122DS 10 57800

[Updated on: Wed, 04 June 2008 07:03]

Report message to a moderator

Re: sql query problem [message #324873 is a reply to message #324850] Wed, 04 June 2008 07:02 Go to previous messageGo to next message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
For 27-May, there are 5+1 = 6 items
So we need four more items

For 25-May, there are three rows with 4,5 and 2 items each.
Is there any logic to find out which one of these three rows we need to select next ?

Regards
Minto
Re: sql query problem [message #324877 is a reply to message #324873] Wed, 04 June 2008 07:13 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
actully above table and its data is just a sample, here you need to check the row which ever comes in the order for '25-may' when ordered DESC on purchase_date column

My real table has also a column of "SRNO"
Re: sql query problem [message #324881 is a reply to message #324872] Wed, 04 June 2008 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> Create table purchase_data ( purchase_date date, item varchar2(15), qty number(6), cost number(12,2))
  2  
SQL> Insert into purchase_data values (’23-may-2008’,’9122DS’,1,5700);
Insert into purchase_data values (’23-may-2008’,’9122DS’,1,5700)
                                  *
ERROR at line 1:
ORA-00911: invalid character

Regards
Michel
Re: sql query problem [message #324903 is a reply to message #324881] Wed, 04 June 2008 08:16 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
i apologize michel

Create table purchase_data ( purchase_date date, item varchar2(15), qty number(6), cost number(12,2));
insert into purchase_data values ('23-may-2008','9122DS',1,5700);
insert into purchase_data values ('25-may-2008','9122DS',4,5700);
insert into purchase_data values ('25-may-2008','9122DS',5,5500);
insert into purchase_data values ('25-may-2008','9122DS',2,5700);
insert into purchase_data values ('27-may-2008','9122DS',1,5900);
insert into purchase_data values ('27-may-2008','9122DS',5,5900);


please help me
Re: sql query problem [message #324909 is a reply to message #324903] Wed, 04 June 2008 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'll be glad to help but I have another problem:
SQL> insert into purchase_data values ('23-may-2008','9122DS',1,5700);
insert into purchase_data values ('23-may-2008','9122DS',1,5700)
                                  *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards
Michel
Re: sql query problem [message #324911 is a reply to message #324909] Wed, 04 June 2008 08:38 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
i have tested these statements these are working on my pc. please check your date format.
Re: sql query problem [message #324914 is a reply to message #324911] Wed, 04 June 2008 08:40 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
try this

Create table purchase_data ( purchase_date date, item varchar2(15), qty number(6), cost number(12,2));
insert into purchase_data values ('23-05-2008','9122DS',1,5700);
insert into purchase_data values ('25-05-2008','9122DS',4,5700);
insert into purchase_data values ('25-05-2008','9122DS',5,5500);
insert into purchase_data values ('25-05-2008','9122DS',2,5700);
insert into purchase_data values ('27-05-2008','9122DS',1,5900);
insert into purchase_data values ('27-05-2008','9122DS',5,5900);

Re: sql query problem [message #324916 is a reply to message #324914] Wed, 04 June 2008 08:47 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I think you are not getting what @Michael is hinting to you. Anything enclosed in quotes is a string and not a date. So if you inserting some values in the date column you should explictily convert it using oracle inbuilt function to_date. You should never rely on the implicit conversion which is happening at your end. So repost it by including the correct functions. For more information check the following link.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions183.htm#SQLRF06132

Regards

Raj

Re: sql query problem [message #324917 is a reply to message #324914] Wed, 04 June 2008 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> insert into purchase_data values ('23-05-2008','9122DS',1,5700);
insert into purchase_data values ('23-05-2008','9122DS',1,5700)
                                  *
ERROR at line 1:
ORA-01843: not a valid month

What I try to say is that a string is NOT a date, NEVER rely on implicity conversion.

Also post your Oracle version (at least 3 decimals), solution depends on it.

Regards
Michel

[Updated on: Wed, 04 June 2008 08:50]

Report message to a moderator

Re: sql query problem [message #324923 is a reply to message #324917] Wed, 04 June 2008 08:59 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
i apologize again

Create table purchase_data ( purchase_date date, item varchar2(15), qty number(6), cost number(12,2));
insert into purchase_data values (to_date('23-may-2008','dd-mon-yyyy'),'9122DS',1,5700);
insert into purchase_data values (to_date('25-may-2008','dd-mon-yyyy'),'9122DS',4,5700);
insert into purchase_data values (to_date('25-may-2008','dd-mon-yyyy'),'9122DS',5,5500);
insert into purchase_data values (to_date('25-may-2008','dd-mon-yyyy'),'9122DS',2,5700);
insert into purchase_data values (to_date('27-may-2008','dd-mon-yyyy'),'9122DS',1,5900);
insert into purchase_data values (to_date('27-may-2008','dd-mon-yyyy'),'9122DS',5,5900);


my oracle version is - Release 9.2.0.1.0
Re: sql query problem [message #324929 is a reply to message #324923] Wed, 04 June 2008 09:07 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Next time if you want to post your oracle version don't type it. Execute the following query.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production


Now coming to your question search check this link and see if you could come up with a solution with that.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions163.htm#i89126

Regards

Raj
Re: sql query problem [message #324930 is a reply to message #324923] Wed, 04 June 2008 09:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
now Michel, if you get an error again, please fix it yourself, you made your point Smile

(@nasir_mughal: "may" is not a valid month in every language)
Re: sql query problem [message #324931 is a reply to message #324923] Wed, 04 June 2008 09:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't have to apologize, you just learn how to post in world wide environment.

Now, it is better to use numbers for month in your example, not every as English for his language or add nls parameters in your TO_DATE function.

Now I have to leave I will come back later.

Regards
Michel
Re: sql query problem [message #324935 is a reply to message #324931] Wed, 04 June 2008 09:22 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
whenever you come back please help me solving this problem

Create table purchase_data ( purchase_date date, item varchar2(15), qty number(6), cost number(12,2));
insert into purchase_data values (to_date('23-05-2008','dd-mm-yyyy'),'9122DS',1,5700);
insert into purchase_data values (to_date('25-05-2008','dd-mm-yyyy'),'9122DS',4,5700);
insert into purchase_data values (to_date('25-05-2008','dd-mm-yyyy'),'9122DS',5,5500);
insert into purchase_data values (to_date('25-05-2008','dd-mm-yyyy'),'9122DS',2,5700);
insert into purchase_data values (to_date('27-05-2008','dd-mm-yyyy'),'9122DS',1,5900);
insert into purchase_data values (to_date('27-05-2008','dd-mm-yyyy'),'9122DS',5,5900);
Re: sql query problem [message #324943 is a reply to message #324935] Wed, 04 June 2008 09:58 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I will leave the last bit for you to do.

  1  select item, cost, qty,
  2      sum(qty) over(partition by item
  3                    order by purchase_date desc, rownum desc) cum_sum
  4* from purchase_data
SQL> /

ITEM                  COST        QTY    CUM_SUM
--------------- ---------- ---------- ----------
9122DS                5900          5          5
9122DS                5900          1          6
9122DS                5700          2          8
9122DS                5500          5         13
9122DS                5700          4         17
9122DS                5700          1         18

6 rows selected.

SQL> define limit=10
SQL> l
  1  select item, cost,
  2       case when cum_sum > &limit
  3       then
  4             &limit - (cum_sum - qty)
  5       else
  6            qty
  7       end qty, cum_sum, qty act_qty
  8  from
  9  (
 10  select item, cost, qty,
 11      sum(qty) over(partition by item
 12                    order by purchase_date desc, rownum desc) cum_sum
 13  from purchase_data
 14* ) where (cum_sum - qty) <= &limit
SQL> /

ITEM                  COST        QTY    CUM_SUM    ACT_QTY
--------------- ---------- ---------- ---------- ----------
9122DS                5900          5          5          5
9122DS                5900          1          6          1
9122DS                5700          2          8          2
9122DS                5500          2         13          5


Regards

Raj
Re: sql query problem [message #325052 is a reply to message #324935] Wed, 04 June 2008 22:49 Go to previous messageGo to next message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
I think we need to use a function to calculate the cost.
Please try this one:

CREATE OR REPLACE FUNCTION get_cost (p_item IN VARCHAR2)
RETURN NUMBER
AS
  n_total_cost NUMBER := 0;
  n_pending NUMBER := 10;
BEGIN
  FOR CUR_DATA IN (SELECT PURCHASE_DATE, ITEM, QTY, COST FROM PURCHASE_DATA WHERE ITEM=p_item ORDER BY PURCHASE_DATE DESC) LOOP
    IF (cur_data.qty < n_pending) THEN
      n_total_cost := n_total_cost + (cur_data.cost * cur_data.qty);
      n_pending := n_pending - cur_data.qty;
    ELSE
      n_total_cost := n_total_cost + (cur_data.cost * n_pending);
      EXIT;
    END IF;
  END LOOP;
  RETURN n_total_cost;
END;

SELECT DISTINCT ITEM, get_cost (item) FROM PURCHASE_DATA;

Regards
Minto
Re: sql query problem [message #325079 is a reply to message #325052] Wed, 04 June 2008 23:53 Go to previous messageGo to next message
rsree.rsree
Messages: 3
Registered: June 2008
Location: Bangalore
Junior Member

You can make use of rowno to find latest 10 records.
select item,sum(cost) from
select cost,item,rn from
(select cost,item,rowno rn from purchase_data where item='9122DS' order by purchase_date)
where rn<11)

Thank You,
Sreekanth
Re: sql query problem [message #325081 is a reply to message #325079] Wed, 04 June 2008 23:57 Go to previous messageGo to next message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
rsree.rsree wrote on Thu, 05 June 2008 10:23
You can make use of rowno to find latest 10 records.



What he wants is the sum of latest 10 items. Not latest 10 records.
A single row can have 4 or 5 items (given in QTY column).

Regards
Minto
Re: sql query problem [message #325095 is a reply to message #325052] Thu, 05 June 2008 00:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why using a function when you can do it in SQL as Raj showed it?

Regards
Michel
Re: sql query problem [message #325142 is a reply to message #325095] Thu, 05 June 2008 02:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could use a row generator to split the purchases down into individual items and get an exact total:
SQL> select sum(cost_per),sum(new_qty)
  2  from  (select r.lvl
  3               ,p.purchase_date
  4               ,p.item
  5               ,p.qty
  6               ,p.cost
  7               ,1 new_qty
  8               ,p.cost/p.qty cost_per
  9               , row_number() over (order by purchase_date desc) rwnum 
 10         from   purchase_data p
 11              ,(select level lvl from dual connect by level<=10) r
 12         where  r.lvl <= p.qty)
 13  where rwnum <= 10;

SUM(COST_PER) SUM(NEW_QTY)
------------- ------------
        16525           10
Re: sql query problem [message #325144 is a reply to message #324943] Thu, 05 June 2008 02:32 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
thank you very much raj for helping me out.
SQL> define limit=10
SQL> select item, cost,
  2         case when cum_sum > &limit
  3         then
  4               &limit - (cum_sum - qty)
  5         else
  6              qty
  7         end qty, cum_sum, qty act_qty,
  8         case when cum_sum > &limit
  9         then
 10               (&limit - (cum_sum - qty))*cost
 11         else
 12              qty*cost
 13         end item_cost
 14    from
 15    (
 16    select item, cost, qty,
 17        sum(qty) over(partition by item
 18                      order by purchase_date desc, rownum desc) cum_sum
 19    from purchase_data
 20   )
 21    where (cum_sum - qty) <= &limit
 22  
SQL> /

ITEM                  COST        QTY    CUM_SUM    ACT_QTY  ITEM_COST
--------------- ---------- ---------- ---------- ---------- ----------
9122DS                5900          5          5          5      29500
9122DS                5900          1          6          1       5900
9122DS                5700          2          8          2      11400
9122DS                5500          2         13          5      11000



is this possible to display the result in one row with SQL, i dont want to use PL
like

Quote:


ITEM QTY ITEM_COST
------------- ----- ---------
9122DS 10 57800

Re: sql query problem [message #325169 is a reply to message #325144] Thu, 05 June 2008 04:14 Go to previous messageGo to next message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
You can do this by modifying the query which Rajaram has posted.

SELECT ITEM, SUM(QTY) QTY, SUM (ITEM_COST) ITEM_COST FROM
(
  select item, cost,
  case when cum_sum > &&limit then
    &&limit - (cum_sum - qty)
  else
    qty
  end qty, 
  case when cum_sum > &&limit then
    (&limit - (cum_sum - qty))*cost
  else
   qty * cost
  end item_cost
  from
  (
    select item, cost, qty,
      sum(qty) over(partition by item order by purchase_date desc, rownum desc) cum_sum
    from purchase_data
  )
  where (cum_sum - qty) <= &&limit
) GROUP BY ITEM;


Regards
Minto
Re: sql query problem [message #325174 is a reply to message #325169] Thu, 05 June 2008 04:26 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
thank you mintomohan
Re: sql query problem [message #325180 is a reply to message #325169] Thu, 05 June 2008 04:33 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks mintomohan for breaking Raj's teaching line.
You are very kind.

Regards
Michel
Previous Topic: Dependent procedures on a table
Next Topic: How to get Error Line Number in PL/SQL in Exception Block
Goto Forum:
  


Current Time: Sun Dec 11 00:12:19 CST 2016

Total time taken to generate the page: 0.07846 seconds