Home » SQL & PL/SQL » SQL & PL/SQL » sql Qurey (Oracle 10g)  () 1 Vote
sql Qurey [message #689375] Sat, 02 December 2023 00:56 Go to next message
amjadem@gmail.com
Messages: 3
Registered: November 2017
Junior Member


Someone please helping me in solving .

Table data between 30-Nov-2023  to 04-Dec-2023

  Descr Tqty       Dqty        ddate
------------------------      --------      ---------  -------------------
WHITE LED DCO                             624        324        30-NOV-23
WHITE LED DCO                             550         20        01-DEC-23
WHITE LED DCO                             530          1        02-DEC-23
WHITE LED DCO                             530                   03-DEC-23
WHITE LED DCO                             530                   04-DEC-23


Query should  display tqty from first date ie 30-Nov-2023 and  sum(dqty)

Required output

  Descr Tqty       Dqty       
------------------------      --------    ---------  
WHITE LED DCO                            624         345  
Re: sql Qurey [message #689376 is a reply to message #689375] Sat, 02 December 2023 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68597
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.


There are many ways to do so, for instance, use SUM in its analytic form.
One question: what is the result if there are several rows for the first date?

Re: sql Qurey [message #689377 is a reply to message #689376] Sat, 02 December 2023 02:36 Go to previous messageGo to next message
amjadem@gmail.com
Messages: 3
Registered: November 2017
Junior Member
first date in not repeated.
Re: sql Qurey [message #689378 is a reply to message #689377] Sat, 02 December 2023 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68597
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Test case?

Re: sql Qurey [message #689379 is a reply to message #689378] Sat, 02 December 2023 09:40 Go to previous messageGo to next message
mathguy
Messages: 105
Registered: January 2023
Senior Member
Whenever you need a single row for each group (here, presumably, grouped by DESCR, although that is a bit odd - you should also have a product ID, not just a "description" of it), think aggregate functions in a query with GROUP BY. The only slightly non-trivial part is getting the "first" TQTY; even that becomes trivial when you learn about the FIRST/LAST aggregate function, but it seems that a very large number of programmers either never heard of it or for some reason choose not to use it.

with
  tbl (descr, tqty, dqty, ddate) as (
    select 'WHITE LED DCO', 624,  324, to_date('30-NOV-23', 'DD-MON-RR') from dual union all
    select 'WHITE LED DCO', 550,   20, to_date('01-DEC-23', 'DD-MON-RR') from dual union all
    select 'WHITE LED DCO', 530,    1, to_date('02-DEC-23', 'DD-MON-RR') from dual union all
    select 'WHITE LED DCO', 530, null, to_date('03-DEC-23', 'DD-MON-RR') from dual union all
    select 'WHITE LED DCO', 530, null, to_date('04-DEC-23', 'DD-MON-RR') from dual
  )
select descr, min(ddate) as first_date, 
       min(tqty) keep (dense_rank first order by ddate) as first_tqty,
       sum(dqty) as sum_dqty
from   tbl
group  by descr
;



DESCR          FIRST_DATE  FIRST_TQTY    SUM_DQTY
-------------  ----------  ----------  ----------
WHITE LED DCO  30-NOV-23          624         345

Re: sql Qurey [message #689380 is a reply to message #689379] Sat, 02 December 2023 11:52 Go to previous message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
Just for fun, match recognize solution:

with tbl(descr,tqty,dqty,ddate)
 as (
     select 'WHITE LED DCO',624, 324,date '2023-11-30' from dual union all
     select 'WHITE LED DCO',550,  20,date '2023-12-01' from dual union all
     select 'WHITE LED DCO',530,   1,date '2023-12-02' from dual union all
     select 'WHITE LED DCO',530,null,date '2023-12-03' from dual union all
     select 'WHITE LED DCO',530,null,date '2023-12-04' from dual
    )
select  descr,
        first_date,
        first_tqty,
        sum_dqty
  from  tbl
  match_recognize(
                  partition by descr
                  order by ddate
                  measures
                    first(ddate) first_date,
                    first(tqty)  first_tqty,
                    sum(dqty)    sum_dqty
                  pattern(p+)
                  define p as 1 = 1
                 )
/

DESCR         FIRST_DAT FIRST_TQTY   SUM_DQTY
------------- --------- ---------- ----------
WHITE LED DCO 30-NOV-23        624        345

SQL>
SY.
Previous Topic: Transform row data into columns
Next Topic: Created schedule based on date conditions
Goto Forum:
  


Current Time: Sat Feb 24 03:50:56 CST 2024