Code for SQL version of a For Loop

Looking for sample code that will create a For loop within SQL that will look at the number of suppliers and if Count(Suppliers) > 1 then loop thru the GPA_Cost to determine the average(GPA_Cost) for that supplier.  Results should be only 1 record per Supplier.

use the following query see related post:

https://www.experts-exchange.com/questions/29096266/Group-by-to-include-an-Avg-Value-within-sql.html?anchor=a42545228&notificationFollowed=206818050#a42545228

Open in new window

Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
SujithConnect With a Mentor Data ArchitectCommented:
You dont need a loop to do this. It can be done with a single aggregate query, as below -

SQL> select * from test_tbl;

SUPPLIER_ID SUPPLIER_NAME                            START_DAT END_DATE  KEY_TYPE             CUR_MTH_COST
----------- ---------------------------------------- --------- --------- -------------------- ------------
   10052838 ABC Company                              01-FEB-18 28-FEB-18 M                            3
   10052838 ABC Company                              01-FEB-18 28-FEB-18 M                            2
   10052838 ABC Company                              01-FEB-18 28-FEB-18 M                            1
   10040517 DEF CORP                                 01-FEB-18 28-FEB-18 M                            3
   10037810 JIM SMITH COMPANY                        01-FEB-18 28-FEB-18 M                            2.79
   10384252 JOSEPH, INC                              01-FEB-18 28-FEB-18 M                            3
   10384252 JOSEPH, INC                              01-FEB-18 28-FEB-18 M                            3
   10384252 JOSEPH, INC                              01-FEB-18 28-FEB-18 M                            2.79
   10046871 COASTAL LLC                              01-FEB-18 28-FEB-18 M                            3
   10258519 CWC CONTROLS                             01-FEB-18 28-FEB-18 M                             2.5
   10258519 CWC CONTROLS                             01-FEB-18 28-FEB-18 M                             2.5
   10048068 AEROSPACE GLIDERS                        01-FEB-18 28-FEB-18 M                            4.75
   10044617 ELECTROIMPACT INC                        01-FEB-18 28-FEB-18 M                             3.5
   10044617 ELECTROIMPACT INC                        01-FEB-18 28-FEB-18 M                            3
   10044617 ELECTROIMPACT INC                        01-FEB-18 28-FEB-18 M                            4
   10044617 ELECTROIMPACT INC                        01-FEB-18 28-FEB-18 M                             4.5
   10400235 ENCORE LLC                               01-FEB-18 28-FEB-18 M                            4

17 rows selected.

SQL>
SQL> select  supplier_id, supplier_name, min(start_date) start_date, max(end_date) end_date, key_type, avg(cur_mth_cost) cur_mth_cost
  2  from    test_tbl
  3  group by supplier_id, supplier_name, key_type
  4  order by 2
  5  ;

SUPPLIER_ID SUPPLIER_NAME                            START_DAT END_DATE  KEY_TYPE             CUR_MTH_COST
----------- ---------------------------------------- --------- --------- -------------------- ------------
   10052838 ABC Company                              01-FEB-18 28-FEB-18 M                            2
   10048068 AEROSPACE GLIDERS                        01-FEB-18 28-FEB-18 M                            4.75
   10046871 COASTAL LLC                              01-FEB-18 28-FEB-18 M                            3
   10258519 CWC CONTROLS                             01-FEB-18 28-FEB-18 M                             2.5
   10040517 DEF CORP                                 01-FEB-18 28-FEB-18 M                            3
   10044617 ELECTROIMPACT INC                        01-FEB-18 28-FEB-18 M                            3.75
   10400235 ENCORE LLC                               01-FEB-18 28-FEB-18 M                            4
   10037810 JIM SMITH COMPANY                        01-FEB-18 28-FEB-18 M                            2.79
   10384252 JOSEPH, INC                              01-FEB-18 28-FEB-18 M                            2.93

9 rows selected.

Open in new window

0
 
SujithData ArchitectCommented:
It is hard to suggest anything without knowing the data model, actual data and expected output(in a simplified form, not your whole data model). Here is the way to write simple loops in Oracle PL/SQL. This is just for illustration only. You might be able to handle your requirement with just one loop.

begin
    for rec in (select * from test_tbl) loop
        -- do something
        for rec2 in (select * from test_tbl2 where id = rec.id) loop
            -- do something else
            dbms_output.put_line(rec2.name);
        end loop;
    end loop;
end;
/

Open in new window

0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Attached is a sample of data.

I need to loop thru the Supplier Name if count >1 then loop thru to avg the cur_mth_cost field.

I only need 1 record per Supplier Name and a single value for the cur_mth_Cost.

if count = 1 then the actual value should be shown.

thanks,
example.xlsx
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Karen SchaeferBI ANALYSTAuthor Commented:
thanks, I have a dumb question - format decimals  What is wrong with the statement:

  , TO_DECIMAL(M_DATA.cur_mth_cost), 3,4) AS CUR_COST

thanks,

Karen
0
 
SujithData ArchitectCommented:
what is the output that you are trying to get?

Try something like this
to_char(cur_mth_cost, '999999.999')
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks
0
 
SujithData ArchitectCommented:
Glad to help. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.