Forum Stats

  • 3,824,981 Users
  • 2,260,447 Discussions
  • 7,896,369 Comments

Discussions

SubQueries

User_7FAON
User_7FAON Member Posts: 31 Red Ribbon

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!

Tagged:

Best Answer

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,030 Red Diamond

    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).

    User_7FAON
  • Jan Gorkow
    Jan Gorkow Member Posts: 144 Gold Badge
    Answer ✓
     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

    User_7FAON
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,030 Red Diamond

    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
    mathguy Member Posts: 10,538 Blue Diamond

    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
    mathguy Member Posts: 10,538 Blue Diamond

    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).