如何从数据库中检索一列数据的总和？ [英] How to retrieve sum of a column of data from the database?

问题描述

如何从数据库中检索一列数据的总和并从数据库的另一列数据中减去它？我想这样做，从Total_Sales列获得总和并从Total_Value列获得总和并执行子操作，即从Total_Sales（Total_Sales - Total_Value）的值中减去Total_Value的值。目前这是我的一段代码。

How to retrieve sum of a column of data from the database and subract it from another column of data from the database? I want to do it like, get the sum from Total_Sales column and sum from Total_Value column and perform subract operation ie, subract value of Total_Value from value of Total_Sales (Total_Sales - Total_Value). Currently this is my piece of code.

calculateButton.addActionListener(new ActionListener()
    {
    public void actionPerformed(ActionEvent ae)
    {

    try
    {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection conn = DriverManager.getConnection("Jdbc:Odbc:TomsJava");

        java.util.Date fromDate=(java.util.Date)fromtextField.getValue();
        java.util.Date toDate=(java.util.Date)totextField.getValue();

        PreparedStatement ps = conn.prepareStatement("select SUM(Total_Sales - Total_Value) As Result from DailyAnalysis where 'Date1 BETWEEN #fromDate# AND #toDate#' ");
        ResultSet rs = ps.executeQuery();
        while(rs.next())
        {

            String itemName = rs.getString("Item");
            Double totalValue = rs.getDouble("Total_Value");
            Double totalSales = rs.getDouble("Total_Sales");

            Double Result = rs.getDouble(1);

        }

    }
    catch(SQLException s)
    {
        System.out.println(s);
    }
    catch(ClassNotFoundException z)
    {
        System.out.println(z);
    }
    catch(NumberFormatException n)
    {
        System.out.println(n);
    }
    }
    });

编辑
我正在尝试检索两个日期之间的列。我想将Total_Value的检索值总和设置为Total Expense文本字段，并将Total_Sales列的总和设置为Total Income文本字段。然后根据它计算损益。

EDITS I am trying to retrieve of the columns between two dates. I want to set the total of retrieved value of Total_Value to the Total Expense text field, and sum of Total_Sales column to the Total Income textfield. Then calculate the profit or loss based on that.

推荐答案

Select Sum(Total_Sales) as Sales_Total, Sum(Total_Value) as Value_Total, Sum(Total_Sales - Total_Value) as Result
From DailyAnalysis

将查询和代码更改为此

     Double result = null;
     Double totalValue = null;
     Double totalSales = null;
      ...

      PreparedStatement ps = conn.prepareStatement("select Sum(Total_Sales) as Sales_Total, Sum(Total_Value) as Value_Total, SUM(Total_Sales - Total_Value) As Result from DailyAnalysis where Date1 BETWEEN ? AND ? ");
            ps.setDate(1,fromDate)
            ps.setDate(2,toDate)
            ResultSet rs = ps.executeQuery();

            //Query should only return one or zero result hence the if statement
            if (rs.next())
            {  
               //Define variables at the top, if you want to have access outside the if block scope
               totalValue = rs.getDouble("Value_Total");
               totalSales = rs.getDouble("Sales_Total");
               result = rs.getDouble("Result");
            }

这篇关于如何从数据库中检索一列数据的总和？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！