From: Courtney Fay <lists@...>
Date: 2012-09-19T07:09:01+09:00
Subject: Ruby SQL Select Sum 2 Columns?

I have the following definition which is looking at an apache database,
and then a sql database.  I need to run a sql statement and sum 2
columns.  When I do this, the result that is getting returned is not
even close.  It's not what it should be.

I search the apache database for a list of projects, then for each
project, I search sql for any records that have that project.  I want to
sum their project costs column, where the project id's are equal.

This is the code:
# CREATE MAP OF PROJECT IDS IN CURRENT OPEN APACHE CASE
t_Case = $current_case.search("special-metadata:ProjectID AND
has-exclusion:0")
t_projectIDs = t_Case.map {|item| item.getspecialMetadata['ProjectID']}
t_projectIDs = t_projectIDs.compact.uniq.sort

connection = java.sql.DriverManager.getConnection(db_url, user_name,
password)

#DEFINITION TO SEARCH THE CURRENT CASE, BY BATCH, FOR ANY DOCUMENTS THAT
HAVE UPDATED TEXT
def select_items(connection, table_oname, tablelog, t_projectIDs)
  $t_projectids = Array.new
  t_projectIDs.each do |projCheck|
    projCosts = $current_case.search("path-name:#{projCheck} AND
previous-version-docid:* AND has-exclusion:0")
    puts projCheck
    dedeupprojCosts = $utilities.item_utility.deduplicate(projCosts)
    projdedupCosts = dedeupprojCosts.count
    puts projdedupCosts.to_s + " is the unique cost sum"
    if projdedupCosts > 0
      puts "in projdedupCosts count is greater than 0"
      t_projdedupinterest = dedeupprojCosts.map { |pages|
pages.getProperties["Interest"] == nil ?
pages.getProperties["InterestVar"] : pages.getProperties["Interst"] }
      t_projdedupinterestSum = t_projdedupinterest.inject(0, :+)
      sqlsame = "SELECT sum(projectCost) AS sumProjCost, sum(interest AS
sumInterest FROM " + table_oname + " WHERE AssetTagBatch =
'#{projCheck}'"
      statement = connection.prepareStatement(sqlsame)
      rows =  statement.execute_query
      #IF THERE IS A RESULT FOR THE QUERY THAT THE SQL OCR TABLE HAS A
MATCHING BATCH AND COUNTS, THEN REPORT TO THE CONSOLE AND LOG TABLE THAT
THE BATCH IS FULLY REPORTED
      if rows != nil
        sqlprojcostsum = sqlsame[0]
        sqlinterestsum = sqlsame[1]
        puts sqlprojcostsum
        puts "that was the sqlprojcostsum"
        exit 0
      end
    end
  end
end

select_items(connection, table_projname, table_namelog, t_projectIDs)
---------------------

If anyone can help, I would appreciate it.

Right now the puts sqlprojcostsum is reporting 83, but for the first
project, it should actually be 1.

-- 
Posted via http://www.ruby-forum.com/.