Count distinct value with 2 criteria

248
February 21, 2018, at 7:14 PM

I am trying to find a JDBC MySQL query that will find distinct values in a particular field, count the number of occurrences of that Feedback and then store it in a array list(if possible or if there are better way to store).

Company      Feedback    Person
------       --------    ------
Apple        Good          A
Pear         Bad           B
Apple        Neutral       C
Orange       Neutral       D
Apple        Bad           E
Apple        Neutral       F
Orange       Bad           G

Expected result for Apple

Company     Feedback     Count
-------     --------     -----
Apple       Good           1
Apple       Neutral        2
Apple       Bad            1

Edited to add in my code:

//Already call an object from database
rs = db.getSM().executeQuery("SELECT COUNT(*) AS 
rowcount FROM predata WHERE company = 'Apple' and feedback LIKE 'G%'");
rs.next();
System.out.println(rs.getInt("rowcount"));
rs = db.getSM().executeQuery("SELECT COUNT(*) AS 
rowcount FROM predata WHERE company = 'Apple' and sentiment LIKE 'N%'");
rs.next();
System.out.println(rs.getInt("rowcount"));

This method works for me but I'm trying to shorten the whole code without repeating the code all over again

Answer 1
SELECT  Company, Feedback, COUNT(Feedback)
FROM t
GROUP BY Feedback
where Company='Apple';
Answer 2

You can query all company-feedback pairs at a time and store them in a Map.

Query all pairs with count from the database.

rs = db.getSM().executeQuery("SELECT company, feedback, COUNT(DISTINCT person) AS rowcount FROM predata GROUP BY company, feedback");

Create a Map and loop through ResultSet to store the count.

HashMap<String, HashMap<String, Integer>> companyFeedbackMap = new HashMap<String, HashMap<String, Integer>>();
while (rs.next()) {
    String company = rs.getString("company");
    String feedback = rs.getString("feedback");
    Integer count = rs.getInt("rowcount");
    if(!companyFeedbackMap.contains(company)){
        companyFeedbackMap.put(company, new HashMap<String, Integer>());
    }
    companyFeedbackMap.get(company).put(feedback, count);
}

To get the count of Apple company with Neutral feedback, simply use companyFeedbackMap.get("Apple").get("Neutral"); // This will return 2.

OR, instead of HashMap<String, HashMap<String, Integer>>, you can create you own Company class with company name, good, neutral and bad as its constructor attributes and store the count from the pivoted query sql in an ArrayList or anything you need like this

rs = db.getSM().executeQuery("SELECT company, SUM(good) AS good, SUM(neutral) AS neutral, SUM(bad) AS bad FROM (SELECT company, CASE WHEN feedback = 'Good' THEN 1 ELSE 0 END AS good, CASE WHEN feedback = 'Neutral' THEN 1 ELSE 0 END AS neutral, CASE WHEN feedback = 'Bad' THEN 1 ELSE 0 END AS bad FROM predata) GROUP BY company");
ArrayList<Company> companies = new ArrayList<Company>();
while (rs.next()) {
    String company = rs.getString("company");
    Integer good = rs.getInt("good");
    Integer neutral = rs.getInt("neutral");
    Integer bad = rs.getInt("bad");
    companies.add(new Company(company, good, neutral, bad));
}

The above sql will return the below result.

Company      good        neutral    bad
------       --------    -------    -------
Apple        1           2          1
Orange       0           1          1
Pear         0           0          1
READ ALSO
How to manipulate responseBody in spring Interceptorsusing using Something like ClientHttpResponseInterceptor?

How to manipulate responseBody in spring Interceptorsusing using Something like ClientHttpResponseInterceptor?

I am receiving a encrypted message for REST API and manipulating the encrypted RequestBody with below interceptor to decrypt it and passing

289
Algebraic signature implementation in Java or C++ Programming Language [on hold]

Algebraic signature implementation in Java or C++ Programming Language [on hold]

Is there any publically available Algebraic signature of a text file using Galois field(2^16) or (2^32) in Java or C++ programming language? Any library which implements such Algebraic signature algorithm for integrity purpose

169