Standard HTML table with total from SQL Server table

Question

Refer to this link: SQLDataReader GetDateTime Format

The result should be:

ID     Number01     TheDate     Number02
----------------------------------------
1      10           01/06/2014  5
2      20           02/06/2014  10
3      30           03/06/2014  15

Is there someone can tell me how I can put the total on each column that contains a number datatype? The total should be on the footer of the table.

For example, 1st footer, is the SUM(number02), then on the 2nd footer, is the average.. AVG(number02).

So I can say, multiple footer.

The result should be:

ID     Number01     TheDate     Number02
----------------------------------------
1      10           01/06/2014  5
2      20           02/06/2014  10
3      30           03/06/2014  15
TOTAL  60           -           30
AVE    20           -           10

Please help. Thank you.

user3401335 · Accepted Answer · 2014-07-08 07:35:33Z

1

you can use this html

<tfoot>
    <tr>
      <td>Tot</td>
      <td>60</td>
      <td></td>
      <td>30</td>
    </tr>
    <tr>
       <td>Avg</td>
       <td>20</td>
       <td></td>
       <td>10</td>
    </tr>
  </tfoot>

this add two lines at the end of the table.

to calculate the total and avarage in the definitions

int totnum1 = 0;
decimal totnum2 = 0;
int numRow = 0;
decimal avg1 = 0;
decimal avg2 = 0;

in the loop

totnum1 += reader.GetInt32(1);
totnum2 += reader.GetInt32(3);
numRow ++;

at the end of loop

avg1 = totnum1 / numRow;
avg2 = totnum2 / numRow;

you could write the html as in the last question using totnum1, totnum2 avg1 and avg2 in place of the number in the example above

public string getWhileLoopData() 
{
 string htmlStr = "";
 SqlConnection thisConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
 SqlCommand thisCommand = thisConnection.CreateCommand();
 thisCommand.CommandText = "SELECT * FROM MyTable WHERE TheDate = @TheDate";
 thisCommand.Parameters.AddWithValue("@TheDate", txtDate.Text);


int totnum1 = 0;
decimal totnum2 = 0;
int numRow = 0;
decimal avg1 = 0;
decimal avg2 = 0;



 thisConnection.Open();
 SqlDataReader reader = thisCommand.ExecuteReader();

 while (reader.Read()) {
     int id = reader.GetInt32(0);

     int Number01 = reader.GetInt32(1);
     DateTime TheDate = reader.GetDateTime(2);
     Decimal Number02 = reader.GetDecimal(3);

     totnum1 += reader.GetInt32(1);
     totnum2 += reader.GetInt32(3);
     numRow ++;

     //string Pass = reader.GetString(2);
     htmlStr += "<tr><td>" + id + "</td><td>" + Number01 + "</td><td>" + TheDate + "</td><td>" + Number02 + "</td></tr>";
 }

 thisConnection.Close();

avg1 = totnum1 / numRow;
avg2 = totnum2 / numRow;

htmlStr += string.Format("<tfoot><tr><td>Tot</td><td>{0}</td><td></td><td>{1}</td></tr>", totnum1 , totnum2 );
htmlStr += string.Format("<tfoot><tr><td>Avg</td><td>{0}</td><td></td><td>{1}</td></tr></tfoot>", avg1 , avg2 );


 return htmlStr;
}

edited Jul 8, 2014 at 7:35

answered Jul 8, 2014 at 6:51

user3401335

2,4152 gold badges23 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Haminteu Over a year ago

hey mate, i didn't get you. could you please add your code to the previous code and submit it here? cheers,

Haminteu Over a year ago

Server Error in '/' Application. Attempted to divide by zero. Line 84: thisConnection.Close(); Line 85: Line 86: avg1 = totnum1 / numRow; Line 87: avg2 = totnum2 / numRow;

user3401335 Over a year ago

how many rows are there? if are 0, then you can add a condition before the division (if(numRow > 0)...

Haminteu Over a year ago

what is numRow++ means? If I have 10 records, and 7 records equal to zero. the average value calculate all the records. It should be only calculate the 3 records with non-zero value.

Collectives™ on Stack Overflow

Standard HTML table with total from SQL Server table

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related