Click here to Skip to main content
15,665,166 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
how to count(sumtotal)of a certain field say quantity depending on year wise.

suppose i have 1 table name facsort and in that i have columns as qty,date,name etc.

now in 2013 year end, how many qty has been inserted in database, i want to display it in gridview.

i dont want month wise how many qty are inserted.

please kindly help.

i will be highly obliged.
thanks in advance.
Posted

You can do this at the query level itself. Just run a total for the year within the query by using a subquery.

E.g.
select *, (select sum(qty) from table where data > '2013/01/01' from table)
 
Share this answer
 
Comments
sudeshna from bangkok 16-Jul-13 1:02am    
shall i write this query under combobox_selectedindexchanged?
sudeshna from bangkok 16-Jul-13 1:03am    
can you give me the full subquery, the example which you gave is not working
sudeshna from bangkok 16-Jul-13 1:35am    
Dim cn As New SqlConnection("Data Source=.\INSTANCE;initial catalog=record;user=sa;password=gariahat")
'Dim da As New SqlDataAdapter
Dim cmd As New SqlCommand
Dim ds As New DataSet


'Try

cmd.Connection = cn
cn.Open()

Dim da As New SqlDataAdapter("select count(qty)from facsort group by return_dt", cn)
ds = New DataSet
da.Fill(ds, "facsort")
For i As Integer = 0 To ds.Tables("facsort").Rows.Count - 1
If ComboBox2.SelectedItem = ds.Tables("facsort").Rows(i).Item("type").ToString() Then
TextBox1.Text = ds.Tables("facsort").Rows(i).Item("qty").ToString()
TextBox2.Text = ds.Tables("facsort").Rows(i).Item("wt_rtn").ToString()
End If
Next
cn.Close()
sudeshna from bangkok 16-Jul-13 1:54am    
can you just expand the subquery?
SQL
select count(qnt) from facsort group by DATEPART(yyyy, date )
 
Share this answer
 
v2
Comments
sudeshna from bangkok 16-Jul-13 1:35am    
i have written this code. but showing error
sudeshna from bangkok 16-Jul-13 1:36am    
i dont have any column as such only year. i have return_dt but it takes value from datetimepicker.
Nirav Prabtani 16-Jul-13 1:42am    
I am not sure but,
try updated solution

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900