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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
SujithData 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.