How Can I sum Vales Of Data From Database

Question

1.00/5 (1 vote)

See more:

hi everyone
i have an database with a column name Lot Number
and according to production there will be multiple Items under the Lot number like weight and date.
so here i want to sum the data (values) of the same Lot number;
so what will be the logic or code.
i have done the selecting part like
"select (weight)from (database) where lot_number='"+textbox1.text+"';
now how can i sum the values under the same lot number.
suppose for lot number 1001
weights are ;100,1000,10000,2000,20000 ..........2000 etc

Posted 7-Jan-14 1:39am

Shantanu sinha

Updated 7-Jan-14 1:46am

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

♥…ЯҠ…♥ · Answer 1 · 2014-01-07T01:46:00

Solution 2

Hi Shantanu,

Its so simple you can try this, simple and sweet ;-)

SQL

sqlCmd = "select SUM(weight) from ([database].tablename) where lot_number='"+textbox1.text+"';
      SqlCommand cmd = new SqlCommand(sqlCmd, conn);
 
       conn.Open();
 
       object resultResult = cmd.ExecuteScaler();
textbox2.text=totalResult.ToString();

Hope this helps you a bit.

Regards,
RK

Posted 7-Jan-14 1:46am

♥…ЯҠ…♥

Updated 7-Jan-14 2:02am

v3

Comments

Shantanu sinha 7-Jan-14 7:51am

and how can i display it in the textbox with TextBox1 (sum)

♥…ЯҠ…♥ 7-Jan-14 7:58am

what is that? could you brief it?

Shantanu sinha 7-Jan-14 8:00am

i have to display this sum into textbox2 so how can i do that
txs a lot

♥…ЯҠ…♥ 7-Jan-14 8:02am

updated my answer, try and lemme know

CPallini · Answer 2 · 2014-01-07T01:47:00

Solution 3

You can do, for instance:
(produces a row with total weight for given lot number)

SQL

select sum(weight) as tot_weight from database where lot_number=..

or

(produces many rows, reporting the total weight of all the lot numbers)

SQL

select sum(weight) as tot_weight from database group by lot_number

Posted 7-Jan-14 1:47am

CPallini

Updated 7-Jan-14 1:48am

v2

Comments

Shantanu sinha 7-Jan-14 7:49am

and how can i display it in the textbox with TextBox1 (sum)

CPallini 7-Jan-14 8:05am

See, for instance
http://forums.asp.net/t/1690372.aspx

BK 4 code · Answer 3 · 2014-01-07T01:43:00

Solution 1

You should refer sql CRUD operation.

This is the simple query.

SQL

Select sum(weights) from table where lot_number='"+textbox1.text+"'

Hi for that purpose you can write your code in datalogic as following

C#

SqlConnection conn = new SqlConnection();
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["connSpionshopString"].ConnectionString;
  
string sqlCmd = "Select sum(weights) as TotalWeight from table where lot_number='"+textbox1.text+"';";
       SqlCommand cmd = new SqlCommand(sqlCmd, conn);

       conn.Open();

       object value = cmd.ExecuteScaler();
        
textbox1.text=value.ToString();
       conn.Close();

Posted 7-Jan-14 1:43am

BK 4 code

Updated 7-Jan-14 1:57am

v3

Comments

Shantanu sinha 7-Jan-14 7:47am

txs for ur reply. i want to sum the values.kindly see the question once again
txs for ur reply and kindly help

Shantanu sinha 7-Jan-14 7:51am

and how can i display it in the textbox with TextBox1 (sum)

BK 4 code 7-Jan-14 7:57am

Please see my Updated solutions

Shantanu sinha 7-Jan-14 8:07am

SqlConnection con = new SqlConnection("Data Source=SHANTANU\\SQLEXPRESS;Initial Catalog=ankur;Integrated Security=True");
// con.Open();
string query = "select sum(Net_Wt) from Packing where Lot_No='" + TextBox1.Text + "'";

SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
con.Open();
object value = cmd.ExecuteScaler();// here i m getting the error

TextBox2.Text = value.ToString();
con.Close();
Response.Redirect("Outwards.aspx");
}

Shantanu sinha 7-Jan-14 10:34am

my weight is in varchar and it is not taking sum any solution for it.

BK 4 code 7-Jan-14 22:58pm

So just convert it to decimal.so your query will be
string query = "select sum(convert(decimal(10,2),Net_Wt)) from Packing where Lot_No='" + TextBox1.Text + "'";