SubQueries

User_7FAON

Select * from A;

No Q R

------ ----- -----

1 F1 L1

2 F2 L2

3 F3 L3

Select * from C;

No JDate EDate NoofDays

----- ------- ---------- -----------

1 01-01-22 05-02-22 1

1 01-06-22 12-06-22 3

3 10-07-22 15-07-22 1

Output :

No Name NoofDays

---- -------- ------------

1 F1L1 4

2 F2L2 0

3 F3L3 1

The Query I tried and Failed :

 With t as

 (

Select a.No, Sum(NoofDays) as NoofDays from A a left outer join C b on a.No = b.No group by a.No

(

 Select No, concat(Q,R) as Name

 (

Select No, regexp_replace(NAME,'( )','') as Name, NoofDays from t

 )

)

)

Note : The tables have only 3 records. Can anyone help me in solving!

Jan Gorkow

 SELECT a.no, a.q || a.r AS name, NVL (SUM (c.noofdays), 0) AS noofdays
  FROM a LEFT OUTER JOIN c ON c.no = a.no
GROUP BY a.no, a.q || a.r
ORDER BY a.no ASC

Best regards,

Jan

Frank Kulash

Ji, @User_7FAON

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

Jan Gorkow

 SELECT a.no, a.q || a.r AS name, NVL (SUM (c.noofdays), 0) AS noofdays
  FROM a LEFT OUTER JOIN c ON c.no = a.no
GROUP BY a.no, a.q || a.r
ORDER BY a.no ASC

Best regards,

Jan

Frank Kulash

Hi, @user_f7aon

I think ypou want something like this

SELECT   a.no
,        a.q || a.r		AS name
,	 NVL ( SUM (c.NoofDays)
	     , 0
	     )			AS NoofDays
FROM      a
LEFT JOIN c ON c.no = a.no
GROUP BY  a.no, a.q, a.r
ORDER BY  a.no, a.q, a.r	-- or whatever you want
;

but it depends on your requirements. Of course, I can't test it without sample data.

mathguy

It makes more sense to aggregate on table C first, and leave the join for the last step. It also makes sense to write the query that way instead of relying on the optimizer to rewrite it for us.

With the sample data created like this:

create table a (no, q, r) as
  select 1, 'F1', 'L1' from dual union all
  select 2, 'F2', 'L2' from dual union all
  select 3, 'F3', 'L3' from dual
;

create table c (no, jdate, edate, noofdays) as
  select 1, date '2022-01-01', date '2022-02-05', 1 from dual union all
  select 1, date '2022-06-01', date '2022-06-12', 3 from dual union all
  select 3, date '2022-07-10', date '2022-07-15', 1 from dual
;

The query and output would look like this:

select a.no, a.q || ' ' || a.r as name, nvl(sq.noofdays, 0) as noofdays
from   a left outer join
       ( select no, sum(noofdays) as noofdays
         from   c
         group  by no ) sq        on a.no = sq.no
order  by no
;

   NO  NAME     NOOFDAYS
-----  -----  ----------
    1  F1 L1           4
    2  F2 L2           0
    3  F3 L3           1

It seems like Fn and Ln are meant as "first name" and "last name". If so, it makes more sense to concatenate them with a space in between; and if so, it makes sense to use || for concatenation, rather than nested calls to CONCAT.

mathguy

By the way: to format your sample input, sample output, and code, select the block of text (with your mouse), then click on the small paragraph icon that appears to the left of the editing window. Click on the double-quote icon, and from the drop-down list select "code block". Then look at the result and adjust the alignment as needed. You can also do this after the fact, with an "edit" - you can find the editing link as three dots to the right of your post, either at the top or at the bottom (at the top for replies; I don't recall for original posts whether that's also at the top or if it's at the bottom).