Removing duplicates in sql without using aggregate functions

Question

I'm starting to learn java and I have a problem with my practice exercise.

This is a snippet of the whole code:

import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.util.ArrayList;
import java.sql.PreparedStatement;

private static final int MAX_LINES      = 44;

private static final int REPORT_COL1    = 30;   
private static final int REPORT_COL     = 15;   

private ArrayList<String>   errors      = new ArrayList<String>();
private ArrayList<String>   detailReport    = new ArrayList<String>();
private ArrayList<String>   summaryReport=  new ArrayList<String>();

String firstSortCode= (String) parameters.get("01");

if(firstSortCode==null || firstSortCode.trim().equals("")) {
    errors.add("Missing required parameter 01");
    invalidParameters= true;
}
else {
    for(int i=0; i<SORT_CODES.length; i++) {
        if(firstSortCode.equals(SORT_CODES[i][0])) {
            pSort1              = SORT_CODES[i][1];
            sort1Attr           = SORT_CODES[i][2];
            sort1GetDescTable   = SORT_CODES[i][3];
            sort1GetDescCode    = SORT_CODES[i][4];
            sort1GetDescValue   = SORT_CODES[i][5];
            val1 = firstSortCode + " - " + pSort1;
            break;
        }
    }
    if(pSort1.equals("")) {
        errors.add("Invalid value '"+ firstSortCode + "' for parameter 01");
        invalidParameters= true;
    }
}

String mainSelectSQL ="SELECT shrdgmr_pidm, " +  
"   shrdgmr_levl_code, "+   
"   shrdgmr_grst_code, " 
"  NVL("+sort1Attr+", 'Not Reported') ";


mainSelectSQL =mainSelectSQL +
    "FROM shrdgmr " + 
    "WHERE shrdgmr_pidm is not null " + 

//Appends the ORDER BY clause
mainSelectSQL+= "ORDER BY "+sort1Attr;


//Executes the query and obtains the ResultSet
ResultSet rs= sqlStatement.executeQuery();

String sort1Desc= "***";            
String Sort1Prev= "*";

//Arrays to hold the student counts for each of the reported sort values
int sort1Count= 0;

int grandTotal = 0;

while(rs.next()) {
    String Value1       = rs.getString(2);  

    if(!Value1.equals(prevSort1Value)) {


    String sort1Record= Library.lPad(sort1Count, REPORT_COL, ' ');

    if(!sort1Desc.equals("***")) {

        if(lineCount[0]+4>MAX_LINES) {
            startNewPage(detailReport, pageCount, lineCount, currentDate, 
                databaseName, pGradTerm, pInitiator, sort1Desc, pSort1, false);
        }

            detailReport.add(Library.rPad(sort1Desc, REPORT_COL1, ' ')+sort1Record);
            summaryReport.add(Library.rPad(sort1Desc, REPORT_COL1, ' ')+sort1Record);
            lineCount[0]++;
        }
        //Resets the counter
        sort1Count =0;


        sort1Desc= getSortDescription(connection, pSort1, sort1GetDescValue, sort1GetDescTable,
                                              sort1GetDescCode, Value1, errors);
    }
sort1Count++;

My question is how to merge duplicate items and sum up their count?

For example the code I have now just prints the following in my summary report,

Architecture 40
Engineering 56
Dentistry 66
Architecture 16
Computer Science 10
Engineering 11
Architecture 5

the output should only be:

Architecture 61
Engineering 67
Dentistry 66
Computer Science 10
Architecture 5

I'm just stuck on how I can do this. I'm thinking of using hashmap or hashset but I'm not sure how. Any help is appreciatd, thank u!

Your output suggests that you have sets of multiple rows in your source table whose results you want to combine -- a.k.a. *aggregate* -- in the returned results. This is exactly the purpose of aggregate functions, so why are you trying to avoid the tool specifically designed for problems such as yours? — John Bollinger, Mar 06 '19 at 18:31
Yes, @Andronicus, unless one is trying to avoid aggregate functions, as the OP says they want to do. — John Bollinger, Mar 06 '19 at 18:32
@JohnBollinger right, aggregating without aggregate functions:) what's the point of having them? — Andronicus, Mar 06 '19 at 18:34

score 1 · Accepted Answer · answered Mar 07 '19 at 07:37

If you really dont want to use the aggregate in sql, you can use HashMap<String, Integer>. Something like that:

Map<String, Integer> aggResult = new HashMap<>();
while (rs.next()) {
    String name = rs.getString(NAME_INDEX); //NAME_INDEX - name column index
    int value = rs.getInt(VALUE_INDEX); //VALUE_INDEX - value column index

    aggResult.merge(name, value, Integer::sum);

    //if you dont have java8 use this "if":
    /*
    if (aggResult.containsKey(name)) {
        Integer sum = aggResult.get(name);
        aggResult.put(name, sum + value);
    } else {
        aggResult.put(name, value);
    }
    */
}
//test output:
aggResult.forEach((key, value) -> System.out.println(key + ": " + value));

Maybe you will need TreeMap (with or without comparator) instead HashMap for order, null-checks on values from ResultSet or something else.

Himanshu · Answer 2 · 2019-03-06T20:15:42.837

0

There had to be aggregate functions involved and theres no charm in having a query which says "aggregate without aggregate"

With this below query does the needful. I have used sum because your o/p doesnt look actually like a count data but sum of already counted records

          SELECT NAME, SUM(*)
           FROM TABLE GROUP BY 
            NAME

edited Mar 06 '19 at 20:15

answered Mar 06 '19 at 19:08

Himanshu

3,830
2
10
29

Removing duplicates in sql without using aggregate functions

2 Answers2