CODEWITHSUNDEEP
javasql
Fame th
Fame th

Reputation: 1038

How to sum data from specific record?

    ___WORKER_ID___|_______OCCUR________
          20       |         1
          20       |         2
          21       |         3
          22       |         5
          20       |         1


    According to this table, I need to know each worker how many occur?
     Such as   Worker_id=20 has occur is 4   
               Worker_id=21 has occur is 8

I can get two value from this Worker table by this code

         String sql = "SELECT Table.worker_id,Table.occur FROM keyword_pages Table"
            + " WHERE keyword_id=" + PUID;

        conn = getConnection();
        stm = conn.createStatement();
        rs = stm.executeQuery(sql);
        while (rs.next()) {
            int kwd_id = rs.getInt(1);    /**Worker_id is 20,20,21,22,20*/
            int occur = rs.getInt(2);     /**Worker_id is 1,2,3,5,1*/          

        }

How to get amount of occur for each worker?

This is my coding. I use hashmap for store each record. and store hashmap to linklist. But I haven't idea for get result.

  conn = getConnection();
        stm = conn.createStatement();
        rs = stm.executeQuery(sql);
        while (rs.next()) {
            int kwd_id = rs.getInt(1);
            int occur = rs.getInt(2);
            hMap.put(kwd_id, occur);
            listID.add(hMap);
            //System.out.println(kwd_id +"  |  "+ occur);
        }

        for (int i = 0; i < listID.size(); i++) {


        }

Upvotes: 0

Views: 80

Answers (2)

laune
laune

Reputation: 31300

Map<Integer,Integer> id2count = new HashMap<>();

while (rs.next()) {
        int kwd_id = rs.getInt(1);    /**Worker_id is 20,20,21,22,20*/
        int occur = rs.getInt(2);     /**Worker_id is 1,2,3,5,1*/          
   Integer count = id2count.get( kwd_id );
   if( count == null ){
       count = Integer.valueOf( 1 );
   } else {
       count += occur;
   }
   id2count.put( kwd_id, count );
}

** Later **

Code to retrieve and print the accumulated values:

for( Integer key: id2count.keySet() ){
    System.out.println( key + ": " + id2count.get(key) );
}

Upvotes: 1

Joop Eggen
Joop Eggen

Reputation: 109623

String sql = "SELECT T.worker_id, SUM(T.occur) AS osum"
        + " FROM keyword_pages T"
        + " WHERE keyword_id=" + PUID
        + " GROUP BY T.worker_id";

Now rs.getInt(2) or rs.getInt("osum") will give the group's sum.

Upvotes: 2

Related Questions