CODEWITHSUNDEEP
sqlsql-servervb.netsql-server-2008
Shmewnix
Shmewnix

Reputation: 1573

How to Total Columns as a row

I have an SQL Query:

Select Location, net_sales, total_visits, Avg_money_per_visit
from Daily_Sales
where day = @day
Group By Location

That is run in VB.NET and stored in a datatable From there, I convert it to HTML and send as the body of an email.

Output

Location       Net_Sales      Total Visits   AVG Money Per Visit
1               100                5                  20            
2                50                2                  25
3               200                2                  100

What I'd like the output to look like with Totals:

Location       Net_Sales      Total Visits   AVG Money Per Visit
1               100                5                  20            
2                50                2                  25
3               200                2                  100
Total:          350                9                  38.89

How can I get the sum column in either SQL, or The datatable prior to converting to HTML.

Here is how i'm converting to HTML:

Public Function DataTableToHTMLTable(ByVal inTable As DataTable) As String
        Dim dString As New StringBuilder
        dString.Append("<table border=""1"" cellpadding=""0"" cellspacing=""0"">")
        dString.Append(GetHeader(inTable))
        dString.Append(GetBody(inTable))
        dString.Append("</table>")
        Return dString.ToString
    End Function

    Private Function GetHeader(ByVal dTable As DataTable) As String
        Dim dString As New StringBuilder

        dString.Append("<thead><tr>")
        For Each dColumn As DataColumn In dTable.Columns
            dString.AppendFormat("<th>{0}</th>", dColumn.ColumnName)
        Next
        dString.Append("</tr></thead>")

        Return dString.ToString
    End Function

    Private Function GetBody(ByVal dTable As DataTable) As String
        Dim dString As New StringBuilder

        dString.Append("<tbody>")

        For Each dRow As DataRow In dTable.Rows
            dString.Append("<tr>")
            For dCount As Integer = 0 To dTable.Columns.Count - 1
                dString.AppendFormat("<td td align='center'>{0}</td>", dRow(dCount))
            Next
            dString.Append("</tr>")
        Next
        dString.Append("</tbody>")

        Return dString.ToString()
    End Function

Upvotes: 1

Views: 80

Answers (2)

A.J
A.J

Reputation: 382

Assuming 'Location' is a varchar field, you can append the total field to your base query to return the sum from DB itself:

--<<Original Query>>
UNION ALL
SELECT 
    'Total:'
    , sum(net_sales)
    , sum(total_visits)
    , sum(Avg_money_per_visit)
from
    Daily_Sales

Upvotes: 0

dfundako
dfundako

Reputation: 8314

You can use WITH ROLLUP in your GROUP BY. Also, you can throw a GROUPING() in there to label the total. 

Select 
CASE WHEN GROUPING(Location) = 1 THEN 'Total' ELSE CAST(location AS VARCHAR(2)) END AS location
, SUM(net_sales) AS net_sales
, SUM(total_visits) AS total_visits
, CAST(AVG(Avg_money_per_visit) AS DECIMAL(10,2)) AS avg_money_per_visit
from Daily_Sales
where day = @day
Group By Location WITH ROLLUP

Upvotes: 1

Related Questions