aggregate Function in Datagrid

girl

Active member
Joined
Jun 11, 2007
Messages
25
Programming Experience
Beginner
have a problem and hope to seek some help and advices.


i have a database which have field names

Date|time|data

the situation is i have time in every half hour interval..

DATE|TIME|NUMBER
02/05|11:00|4
02/05|11:30|5
02/05|12:00|6
02/05|12:30|5


i want to add row time 11:00 and 11:30 @ column NUMBEr together , 12:00 and 12:30 @ column number and so on. after this addition, find the max of the sum value and display.

so for e.g for 11:00 and 11:30 the outcome is 9 then compare wif 12:00 and 12:30 the outcome is 11.. comparing 9 and 11, the max is 11. display in datagrid.

are there any reference or sample codes for reference?

help is greatly appreciated!!
 
DataSet - Used as an offline reference to the database (mimics the structure)
DataAdapter - Used to fill in your dataset
Connection - Used to connect to the database

With these three objects you should be able to create everything you want.

You should just play around with the System.Data namespace a little bit and it should be easy to figure out. Because you did not specify which type of database it was I could not provide specific code. You just have to add the data source to your project through the wizard provided by .NET and play around with the object provided in the System.Data namespace relating to the type of database you are working with.

As for the actual logic of how to add objects it could be accomplished with a for statement.

(based on 24 hour time otherwise you have to add AM or PM)
For x as integer = 1 to 24
'This is the query string
'"Select Number From `yourtable` Where Date = '05/02' and Time Like '%" & x.tostring() & "%'"
'This will return the two records on that date for each hour then you just have the dataReader loop through the records and add them together. If you wish to exclude date from the search just leave that out of the where statement and it will return all the times that start with X. I would be happy to provide you with further assistance I just need some more detail about which type of database you are using. You can even send me the database if you want. I'm bored and at work. LOL

Next



Steve
 
oh hihi steve

m using SQL server 2000 and visual basic 2005. yeah i have the connection up. m using ODBC adapter and got this working.

thnx for your help!!! greatly appreciate if there are sample codes!! hope to hear from you soon. will try out the logic u wrote and get back! hee.
 
For x As Integer = 1 To 24

'This will return the two records on that date for each hour
sqladapter.SelectCommand = New OdbcCommand("select abc from " & table & " where Date="05/01/2007" and Time Like '%" & x.ToString() & "%'", conn)
dr = mycommand.ExecuteReader


Next


ds.Tables.Add("" & table & "")

sqladapter.Fill(ds, "" & table & "")

DataGridView1.DataSource = ds.Tables("" & table & "")
sqladapter.Dispose()

where "& table &" is my table name. i m unsure to how to add.. which variable should i use to add the sum??
 
oh btw steve, do you know how to select a date range using month calendar or datetimepicker?

currently moi program works fine with one but when i wana to select a range, it did not display the results i want

i use

sqladapter.SelectCommand = New OdbcCommand("SELECT * from " & table & "where date between @from_date and @to_date", conn)


sqladapter.SelectCommand.Parameters.Add("@from_date", OdbcType.NVarChar, "date").Value = Me.MonthCalendar1.SelectionRange.Start.ToShortDateString("mm/dd/yyyy")


sqladapter.SelectCommand.Parameters.Add("@to_date", OdbcType.NVarChar, "date").Value = Me.MonthCalendar2.SelectionRange.Start.ToShortDateString("mm/dd/yyyy")

i was prompt date cannot convert integer error.

i greatly appreciate if you can help me. thnx!
 
DATE|TIME|NUMBER
02/05|11:00|4
02/05|11:30|5
02/05|12:00|6
02/05|12:30|5


Helpfully, you dont say what datatypes DATE and TIME are.. I'll assume they are strings:

VB.NET:
SELECT TOP 1 * FROM
(
SELECT
  SUBSTRING(time, 1, 2) as hr
  SUM(number) as sm
FROM
  table
WHERE
  date = '02/05'
ORDER BY sm
)
 
It's SQL

It selects the top 1 record from
(a list of all the hours in a particular day, and the sum of all Number occurring in that hour)


THis is a relatively simple SQL and it reads quite like english; I cant quite understand where you would get stuck with it, but here is what the database will do:

Find all records from that date
Chop the first 2 numbers off the hour
Group up by the hour, adding all the numbers in that hour
Sort the list into descending order (Z->A)*
Take just the top entry from this list, as it is the hour with the highest number that day

Which is what you asked for

*due to a small typo, the list will be sorted A->Z. you need to ORDER BY sm DESC to get it to appear in Z->A order
 
hihi cjard,

i have read up from the internet and understand the query. when i tried the code in the query analyser, it prompt me this error

The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
 
Oh, what a lame product SQL Server is. Bah.

You can try:

VB.NET:
SELECT
  SUBSTRING(time, 1, 2) as hr
  SUM(number) as sm
FROM
  table
WHERE
  date = '02/05'
GROUP BY 
  SUBSTRING(time, 1, 2)
ORDER BY sm

and then only use the top row in your client code.

You could also try it using analytics; those queries are much harder to write out of my head, and I dont have your database, so I wont attempt it here.. I'd need to have your data locally to write a query using analytic functions..
 
Oh, what a lame product SQL Server is. Bah.

You can try:

VB.NET:
SELECT
  SUBSTRING(time, 1, 2) as hr
  SUM(number) as sm
FROM
  table
WHERE
  date = '02/05'
GROUP BY 
  SUBSTRING(time, 1, 2)
ORDER BY sm

and then only use the top row in your client code.

You could also try it using analytics; those queries are much harder to write out of my head, and I dont have your database, so I wont attempt it here.. I'd need to have your data locally to write a query using analytic functions..

hihi i tried this codes and it is working....
VB.NET:
select site_ne_Name,cell_network_id,substring (time,1,2) as hour, sum(IC_Attempt_EBTS)as sm
	from IC_Attempt_EBTS
where Site_NE_Name= 'S013_Ayer_Rajah_Ex'
Group by substring (time,1,2),site_ne_name,cell_network_id

but the results i have are all the results from 00:00 to 23:00. and as one Site_NE_name has 3 Cell_network _id, therefore i must display 3 MAx results. How should i do it? using MAx Function? where should i put it?
 
VB.NET:
Select  date,Site_Ne_Name,Cell_network_Id ,Max(sm)as Attempt FROM
(
	select date,site_ne_Name,cell_network_id,substring (time,1,2) as hr, sum(IC_Attempt_EBTS)as attempt
	from IC_Attempt_EBTS
	Group by date,substring (time,1,2),site_ne_name,cell_network_id

)
IC_Attempt_EBTS
group by Site_ne_name,Cell_network_id,Date,hr;

hi hi i have a question to ask.. i have this set of codes working and my resultset is
date Site Cell attempt
05/01/2007 S013 1 12
05/01/2007 S013 2 21
05/01/2007 S013 3 24

how do i get the accompanying hour??? i cant seem to get..when i put max(hr) is show me 23:00 which is the max (hr).
this is the last part to allow function to work perfectly!
 
That code cannot work:

VB.NET:
Select  date,Site_Ne_Name,Cell_network_Id ,Max(sm)as Attempt FROM
(
	select date,site_ne_Name,cell_network_id,substring (time,1,2) as hr, sum(IC_Attempt_EBTS)as attempt
	from IC_Attempt_EBTS
	Group by date,substring (time,1,2),site_ne_name,cell_network_id

)
IC_Attempt_EBTS
group by Site_ne_name,Cell_network_id,Date,hr

In the outer query, you select MAX(sm) but sm is not present in the select list of the inner query

If you want the hr, select it!!
 
That code cannot work:

VB.NET:
Select  date,Site_Ne_Name,Cell_network_Id ,Max(sm)as Attempt FROM
(
	select date,site_ne_Name,cell_network_id,substring (time,1,2) as hr, sum(IC_Attempt_EBTS)as attempt
	from IC_Attempt_EBTS
	Group by date,substring (time,1,2),site_ne_name,cell_network_id

)
IC_Attempt_EBTS
group by Site_ne_name,Cell_network_id,Date,hr

In the outer query, you select MAX(sm) but sm is not present in the select list of the inner query

If you want the hr, select it!!

oops sorry its max(attempt).. i select hr in my select query however it display everything.. i only want to select max results and hr of the max results..
 
Back
Top