How to write a query that will retrieve the items with sold amount

oracle

Problem : I have a table which has three columns such as
item id ,transaction_date,Amount_sold.

I want to have a list of item with Amount sold which are sold today but if the same item was sold yesterday then it should not come in my list.

Best Answer

Not exists will work here

 select item_id, amount_sold 
       from table t
   where transaction_date = trunc(sysdate) 
     and not exists (select 1
                       from table t1
                      where t1.item_id = t.item_id
                          and t1.transaction_date = trunc(sysdate)-1;

EDIT based on OP's latest observations :

select item_id, SUM(amount_sold) as amount
       from table t
   where trunc(transaction_date) = trunc(sysdate) 
     and not exists (select 1
                       from table t1
                      where t1.item_id = t.item_id
                          and trunc(t1.transaction_date) = trunc(sysdate)-1
 group by item_id;

Related Solutions

How to group results in batches of consecutive rows with the same ‘module’

The question is which of the pdates for a module do you want to use for ordering purposes?

If you are happy to use the largest, you could use:

select module from logging order by max(pdate);

--- EDIT

I think I get it now - you probably want something like this:

select module
from( select module, 
             sum(module_step) over (order by pdate rows unbounded preceding) 
                 as batch_number, 
             pdate
      from( select module, 
                   case lag(module) over (order by pdate) when module then 0 
                        else 1 end as module_step, 
                   pdate from logging) )
group by module, batch_number
order by batch_number;

Simple Oracle query hangs depending on WHERE clause

following the advice of a_horse_with_no_name,

Please show us the execution plan for the second query – a_horse_with_no_name 4 hours ago

I have posted the relevenat parts of the execution plan... however I can't make heads or tails of what is wrong with it from that....

I followed Mat's advice and guess that lack of indexing is the issue. Because there is no indexing, it must be as Matt says, that this simple query is doing a full table scan, which could take longer than the few minutes I have been waiting for the query to complete...

Useful link about the concept of indexing: http://www.orafaq.com/node/1403

Is that column indexed? Otherwise you'll get a full table scan, which can take a while if the table is big. – Mat 7 hours ago

select index_name, column_name from dba_ind_columns where index_owner = 'THE_SCHEMA' and table_name = 'YOUR_TAB' order by index_name will get you all the indexed columns. Look at the explain plan to see what it is doing (look into set autotrace or use a GUI that has that feature, like sqldeveloper) – Mat 7 hours ago

Thanks for your help guys. Let me know if I might be wrong in that assumption, or if you might have additional insight a_horse_with_no_name....

Best Answer

Related Solutions

How to group results in batches of consecutive rows with the same ‘module’

Simple Oracle query hangs depending on WHERE clause

Related Question