How would I change this code to make the data group properly?

Question

Here is my code, it does its job, mostly but it isn't grouping like I told it to using an SQL statemnt. Also I was wondering how I could make a custom column that numbers itself so that the top is '1' and it numbers down from there.

btnLeaderBoardUpdate.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent arg0) {
            String sql3 ="Select Name, Kills from honscores group by Name, Kills order by Kills DESC";
            ResultSet rs;
            try {
                st = conn.prepareStatement(sql3);
                rs = st.executeQuery();
                table_2.setModel(DbUtils.resultSetToTableModel(rs));

            } catch(Exception e) {
                JOptionPane.showMessageDialog(null, e);
            }

        }
    });

Here is the output:( I want to add a column before the current two called rank and have it number from 1)

Name           Kills

Raknath        20

AceFire6       15

AceFire6       12

AceFire6       10

Raknath        9

Q22            7

Q22            5

score 2 · Accepted Answer · edited May 23 '17 at 12:29

For your first issue, see below.

Short answer
Use this statement instead:

Select Name, sum(Kills) from honscores group by Name order by Kills DESC

Long answer
You do not want to group by kills but only by name and want to aggregate the result.

Assuming you have this data:

AceFire6  2
AceFire6  2
AceFire6  1
AceFire6  3
Raknath   1
Raknath   1

and use your statement, the database will group all values for AceFire6 that have a kills value of 2, all values for AceFire6 that have a kills value of 1, etc. and then select the amount of kills.

So in the end, you will get this result:

AceFire6  3
AceFire6  2
AceFire6  1
Raknath   1

What you want is to sum the kills up, no matter how many the person had per - I assume - game.

For your second issue (the number of rows) see this answer of another question.

Edit:
Alright, something like this should work (no guarantees, I did not test the statement):

SELECT @rn:=@rn+1 AS rank, Name, kills
FROM (
  Select Name, sum(Kills) as kills from honscores group by Name order by Kills DESC
) t1, (SELECT @rn:=0) t2;

Happy FPS-ing... :)

This worked exactly as I wanted it to :D Thank you very much! — AceFire6, Nov 20 '11 at 16:01

Cyclonecode · Answer 2 · 2017-07-16T05:34:39.223

0

Try this:

// first query
SET @ranked = 0;
// second query
SELECT @ranked:=@ranked+1 ranked, name, kills FROM honscores 
GROUP BY name, kills 
ORDER BY ranked ASC;

And as @LeChe saied you can use SUM(kills) in your field list to get the total kills for each name.

SELECT @ranked:=@ranked+1 ranked, 
       name, 
       SUM(kills) kills FROM honscores, (SELECT @ranked:=0) t2
GROUP BY name
ORDER BY ranked ASC;

edited Jul 16 '17 at 05:34

answered Nov 20 '11 at 12:12

Cyclonecode

29,115
11
72
93

I think that in his code, he will have problems with the `SET` before the actual `SELECT` - my guess is he wants one statement. :) – LeChe Nov 20 '11 at 12:15

How would I change this code to make the data group properly?

2 Answers2