SQL如何在网格视图中显示同一列中一列的总和

Question

我正在使用VB.Web应用程序，我正在尝试获取输出中同一列末尾的所有列值的总数。例如，我有一个有多个价格的专栏，在专栏的末尾得到这些价格的总和。就我而言，我想对D.ORIG_PRC.也这样做

有没有一种方法可以通过使用这种将值传递到gridview的方法来做到这一点？

还是我最好使用ASP控件将值输出到gridview？

查询

Protected Sub ExecuteButton_Click(sender As Object, e As EventArgs) Handles ExecuteButton.Click
Dim StoreID As Integer
Dim TransID As Integer
Dim RgstID As Integer
Dim dt As DataTable
If Not Integer.TryParse(StoreIDTextbox.Text, StoreID) Then
MsgBox("Invalid input. Please enter both Store ID and Transaction ID.")
Exit Sub
End If
If Not Integer.TryParse(TransactionIDTextbox.Text, TransID) Then
MsgBox("Invalid input. Please enter both Store ID and Transaction ID.")
Exit Sub
End If
SQL.AddParam("@Str_ID", StoreID)
SQL.AddParam("@Tran_ID", TransID)
'Rgst_ID Validation
If RegisterIDTextbox.Text.Length = 0 Then
MsgBox("RgstID has no value")
SQL.AddParam("@Rgst_ID", "")
ElseIf RegisterIDTextbox.Text.Length > 0 Then
MsgBox("RgstID has a value")
RgstID = Integer.Parse(RegisterIDTextbox.Text)
SQL.AddParam("@Rgst_ID", RgstID)
End If
Try
dt = SQL.ExecQuery(" Select H.Emp_ID, H.Cust_ID, H.Rgst_ID, D.TRAN_LN_NUM AS Line#, D.DISC_CD AS Disc_CD, D.AUTH_EMP_ID AS Auth_Emp_ID, CAST(D.ORIG_PRC AS DECIMAL(19,2)) AS Orig_Prc, CAST(D.DISC_AMT AS DECIMAL(19,2)) AS Disc_Amt, D.DISC_PCT AS Disc_Pct, D.GL_ACCT_ID AS GL_Acct_ID
From Transaction_Header H 
INNER Join LN_Detail L On (H.Str_ID = L.Str_ID And H.Rgst_ID = L.Rgst_ID And H.Tran_ID = L.Tran_ID)
INNER Join LN_Discount D ON (L.Str_ID = D.Str_ID And L.Rgst_ID = D.Rgst_ID And L.Tran_ID = D.Tran_ID And L.Tran_LN_Num = D.Tran_LN_Num)  
WHERE(H.Str_ID = @Str_ID)
And (H.Tran_ID = @Tran_ID)
And ((H.Rgst_ID = @Rgst_ID) Or (@Rgst_ID Is NULL Or @Rgst_ID = ''))")
Catch ex As Exception
MsgBox(ex.Message)
Exit Sub
End Try
GridView1.DataSource = dt
GridView1.DataBind()
TimeLabel.Text = DateAndTime.Now
End Sub

SQL控制

Private ReadOnly ConStr As String = ""
Private DBCmd As SqlCommand
'Query Parameters
Public Params As New List(Of SqlParameter)
'This generates a blank sqlclient class with the deafult connection string
Public Sub New()
End Sub
'Allow connection string override
Public Sub New(connectionString As String)
ConStr = connectionString
End Sub
'Execute Query Sub
Public Function ExecQuery(query As String) As DataTable
Dim DBDT = New DataTable
Using DBCon As New SqlConnection(ConStr),
DBCmd As New SqlCommand(query, DBCon)
Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
Params.Clear()
DBCon.Open()
DBDT.Load(DBCmd.ExecuteReader)
End Using
Return DBDT
End Function
'Add Params
Public Sub AddParam(Name As String, Value As Object)
Dim NewParam As New SqlParameter(Name, Value)
Params.Add(NewParam)
End Sub 

Answer 1

根据Fabio的评论：

'after you execquery()
Dim sum = dt.Compute("SUM([Orig_Prc])")
Dim r = dt.NewRow()
dt("Orig_Prc").Value = sum
dt.Rows.Add(r)

如果您的任何列都是AllowDBNull = False，那么您必须为那些太的列设置值

---

您也可以在DB中通过UNIONing对SELECT执行此操作，该SELECT对所需列求和。如果你这样做，我建议你添加一列进行订购，并使详细行具有0，总行具有1，这样你就可以按它订购，并确保总行显示在底部