Handle Mutiple Rows

I am Aggregating the data on 'INTERIM_AUTO' table on the basis of Unit ID, Line ID, CURR_DATE i.e.

SELECT INTERIM_AUTO_ID,
       UNIT_ID,
       PROD_LINE_ID,
       CURR_DATE,
       BAL_AMOUNT,
       V_SOURCE_CODE,
       CURR_ID,
       CREATED_BY,
       UPDATED_DATETIME
  FROM INTERIM_AUTO
 WHERE CURR_DATE = to_date(V_DATE, 'MM/DD/YYYY')
 GROUP BY UNIT_ID,
          PROD_LINE_ID,
          CURR_DATE

And then if we get multiple entries for the above combination then I have to do the summation of BAL_Amount and consider the single entry for the above combination with this calculated Amount. How do I achieve this ?

Answers


It sounds like you want to calculate the sum of the BAL_AMOUNT column. You can do this with the SUM function.

If you use SUM on your BAL_AMOUNT column, you will have to add all your other columns into the GROUP BY:

   SELECT INTERIM_AUTO_ID,
          UNIT_ID,
          PROD_LINE_ID,
          CURR_DATE,
          SUM(BAL_AMOUNT) as SUM_BAL_AMOUNT,
          V_SOURCE_CODE,
          CURR_ID,
          CREATED_BY,
          UPDATED_DATETIME
     FROM INTERIM_AUTO
    WHERE CURR_DATE = to_date(V_DATE, 'MM/DD/YYYY')
 GROUP BY UNIT_ID,
          PROD_LINE_ID,
          CURR_DATE,
          INTERIM_AUTO_ID,
          UNIT_ID,
          PROD_LINE_ID,
          V_SOURCE_CODE,
          CURR_ID,
          CREATED_BY,
          UPDATED_DATETIME;

Or remove the columns you don't need:

   SELECT UNIT_ID,
          PROD_LINE_ID,
          CURR_DATE,
          SUM(BAL_AMOUNT) as SUM_BAL_AMOUNT
     FROM INTERIM_AUTO
    WHERE CURR_DATE = to_date(V_DATE, 'MM/DD/YYYY')
 GROUP BY UNIT_ID,
          PROD_LINE_ID,
          CURR_DATE;

Need Your Help

pjax_rails with create, update, and destroy actions

jquery ruby-on-rails ruby-on-rails-3.2 pjax

Background: Building a small app where the intention is that full page loads do not happen after initially arriving to the app. Implemented pjax_rails plugin. However create, update, and destroy ac...

How to access commit message from Mercurial Input or Changeset hook

python mercurial hook

I would like to write a hook for Mercurial to do the following, an am struggling to get going.:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.