Home » SQL & PL/SQL » SQL & PL/SQL » sql Qurey (Oracle 10g)
( ) 1 Vote
sql Qurey [message #689375] |
Sat, 02 December 2023 00:56  |
 |
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 #689379 is a reply to message #689378] |
Sat, 02 December 2023 09:40   |
 |
mathguy
Messages: 108 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  |
Solomon Yakobson
Messages: 3303 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.
|
|
|
Goto Forum:
Current Time: Fri Feb 07 10:59:26 CST 2025
|