如何获取“总计”信息？代表结果集中总和的变量 [英] How to retrieve the "total" variable representing a sum in a resultset

问题描述

我正在制作一个JavaFX / SQL程序，表示客户与客户支出之间的数据。我正在尝试计算特定客户的总客户支出，并在Javafx文本区域中输出。

我的交易表具有日期，描述，金额，客户名和Transaction_ID，其中Transaction_ID是主键。

这是我的方法 calculate_client_spending Result_transaction是我的全局TextArea变量：

  public void compute_client_spending（）{
 try {
 ConnectionClass Databaseloader = new ConnectionClass（）; 
 Databaseloader.getConnection（）; 
字符串sql = SELECT t.Amount，t.Clientname\n + 
 FROM Transactions AS t\n + 
 JOIN（SELECT Clientname，SUM（Amount）AS total\n + 
 FROM Transactions\n + 
 WHERE Clientname ='Gaylord420'）AS s on t.Clientname = 
 s.Clientname; 
 ResultSet rs = Databaseloader.executeSQLRequestCommand（sql）; 
 
 rs.next（）; 
 Results_trasactions.setText（）; 
 Results_trasactions.setText（今天的总利润为： + 
 rs.getInt（ total）））; 
} 
} 
 catch（SQLException e）{
 System.err.println（遇到异常！）; 
 System.err.println（e.getMessage（））; 
} 
} 
  

方法方法返回：
Got例外！找不到总计列。

似乎认为总计是一列，但它是别名，而不是我的交易表中的属性。总数应为最终查询的结果。如果有帮助，我的查询就是基于此帖子构建的：

如何对SQL中具有相同ID的列求和？$

任何帮助都会很棒。在这个问题上停留了一个月了。

解决方案

您没有在<$ c中包括列 total $ c>选择列表，因此在您的代码中仅返回两列：金额和 Clientname 。

更改为此：

 字符串sql = SELECT t.Amount，t。客户名称，s.total\n + 
 .................................... ....................... 
  

I'm making a JavaFX/SQL program representing Data between clients and clients spending. I am trying to calculate the total client spending by a specific client and output in the Javafx text area.

My Transactions table, has Date, Description, Amount, Clientname, and Transaction_ID where Transaction_ID is the primary key.

Here is my method "calculate_client_spending" "Result_transaction" is my global TextArea variable:

public void calculate_client_spending(){
        try {
        ConnectionClass Databaseloader = new ConnectionClass();
        Databaseloader.getConnection();
       String sql = "SELECT t.Amount, t.Clientname\n" +
      "  FROM Transactions AS t\n" +
      "  JOIN (SELECT Clientname, SUM(Amount) AS total\n" +
      "          FROM Transactions\n" +
       "        WHERE Clientname = 'Gaylord420') AS s ON t.Clientname = 
      s.Clientname";
      ResultSet rs =  Databaseloader.executeSQLRequestCommand(sql);

          rs.next();
          Results_trasactions.setText("");
          Results_trasactions.setText("The total profits today are: " + 
      rs.getInt("total"));
        }
        }
     catch (SQLException e) {
        System.err.println("Got an exception! ");
        System.err.println(e.getMessage());
    }
    }

The method method returns: "Got an exception! Column 'total' not found."

It seems to think "Total" is a column, but it is an alias, not an attribute in my "Transactions" Table. The total is supposed to the result of the final query. If it helps, my query was constructioned based on this post:

How to take sum of column with same id in SQL?

Any help would be great. Been stuck on this problem for a month now.

解决方案

You did not include the column total in the select list so in your code there are only 2 columns returned: Amount and Clientname .
Change to this:

String sql = "SELECT t.Amount, t.Clientname, s.total\n" +
...........................................................

这篇关于如何获取“总计”信息？代表结果集中总和的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！