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!!
 
Allow me to neaten up your SQL code. You will find it a lot easier if you adopt some set convention for writing SQLs. VB.NET keeps your code neat for you, and it doesnt make SQL neat, so you should do that yourself... Plus other developers will prefer to read nicely formatted code:


So, you want to find the MAX attempt, per date, per cell network id, and the hour that it is associated with? It thus gets more complex:
VB.NET:
SELECT
  i.date,
  i.site_ne_name,
  i.cell_network_id,
  i.hr 
  i.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
  ) i
  INNER JOIN
  (
   SELECT
     date,
     site_ne_name,
     cell_network_id,
     MAX(attempt)as max_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
   
   ) 
   GROUP BY 
     site_ne_name,
     cell_network_id,
     date
  ) find_max
  ON
    i.date = find_max.date AND
    i.site_ne_name = find_max.site_ne_name AND
    i.cell_network_id = find_max.cell_network_id
WHERE
  i.attempt = find_max.max_attempt

Owing to the increasing complexity of this query, if you have any problems with it, please post it nicely formatted - i wont read it if it's turned into a garbled mash spread over 5 lines.

There is no SQL coding style standard, but what I've posted here is one our company adopts internally:
SQL reserved words and functions in upper case
Everything else in lower case
Sections of SQL (select is a section, from is a section, where is a section etc) have the SQL reserved word as a header and they are all nicely indented another level
Subqueries are given their own set of brackets and indented another level

You dont have to adopt exactly this standard, but please post readable code
 
hey cjard. sorry for the messy codes.. i tried the codes and edited abit n it works welL!! thnx lotz.. but just a question to ask..

lets say for e.g after this step, i have gathered all the max hourly sum of ic_attempt_ebts of different sites_ne_name

do i attempt a loop or a simple sql for the following tasks??

add up all the 00 hour of site_ne_name and compare with sum of 01hr to 23hr.

n lastly thnx for your help!!
 
I dont really understand the requirement, but you can do a query like:

VB.NET:
SELECT
  site_ne_name,
  SUM( CASE WHEN hour = '00' THEN attempt ELSE 0 END ) as sum_00_hours,
  SUM( CASE WHEN hour <> '00' THEN attempt ELSE 0 END ) as sum_other_hours
FROM
  table
GROUP BY
  site_ne_name

Working this into your original query might be possible too, but really.. Dont keep asking me to build this query bit by bit because something I do now may make the later part hard. If you want this query to do any more work, tell me all of it, and try to explain it well, with example inputs and outputs
 
I dont really understand the requirement, but you can do a query like:

VB.NET:
SELECT
  site_ne_name,
  SUM( CASE WHEN hour = '00' THEN attempt ELSE 0 END ) as sum_00_hours,
  SUM( CASE WHEN hour <> '00' THEN attempt ELSE 0 END ) as sum_other_hours
FROM
  table
GROUP BY
  site_ne_name

Working this into your original query might be possible too, but really.. Dont keep asking me to build this query bit by bit because something I do now may make the later part hard. If you want this query to do any more work, tell me all of it, and try to explain it well, with example inputs and outputs



thnx for your prompt reply cjard. actually i hope to do the following . hope this illustration is clear for you.

VB.NET:
[FONT="Courier New"]Site_ne_name | Cell_network_Id | Hr | Attempt
S012                1                       00    2
S013                256                    00    2
S014                1                        00   3
S012                1                       01    2
S013                256                    01    2
S014                1                        01   3[/FONT]

i hope to add up all the 00 hr col Attempt together thn followed by 01hrs then 02hrs up till 23 hrs.. compare the max data obtained and display the max hr Site_Ne_name, Cell_network_Id ,Hr and Attempt.
 
HIHI.cjard sorry for the unformatted info. i had attached a doc file for a clearer e.g

i hope to add up the column HR for all the SITES every hourly from 00 to 23hrs, compare and get the MAX value for attempt. when i get the max value, for e.g @ 11 hrs, display all SITES, HR, CELL_NETWORK_ID, ATTEMPT of 11 hrs.
 

Attachments

  • SITE.doc
    27 KB · Views: 33
Your spec doesnt make any sense, sorry. Please rewrite it.

in the doc, there are 3 different sitenames with different cell_network_id. the table attached is a sample of the table results retrieved from the previous codings you had assisted me in.

i hope to add up column attempt meaning for col hour 00, add up 1 + 1 + 2 then for col hour 01 add up col attempt 1+1+2 and 02hrs and so on. compare all the values and get max(attempt). for e.g max(attempt) is found to be at 11hrs.

i hope to display all SITE_NETWORK_ID,SITENAME,CELL_NETWORK_ID,HR and ATTEMPT of 11hrs.
 
The document provided, doesnt contain any entries for an hour of 11. If youre going to provide sample data, please provide data that will eventually be used in the output of the query! Dont just give me lines 1 - 6 and then say "write me a query that outputs line 10"
 
in the doc, there are 3 different sitenames with different cell_network_id. the table attached is a sample of the table results retrieved from the previous codings you had assisted me in.

i hope to add up column attempt meaning for col hour 00, add up 1 + 1 + 2 then for col hour 01 add up col attempt 1+1+2 and 02hrs and so on. compare all the values and get max(attempt). for e.g max(attempt) is found to be at 11hrs.

i hope to display all SITE_NETWORK_ID,SITENAME,CELL_NETWORK_ID,HR and ATTEMPT of 11hrs.

Translating this into "cjard speak" I think I get:

For all site network ids, site names, cell network ids etc group by the hour regardless of the day, then find the hour with the highest attempt, then find a list of all sites and attempts in that hour, regardless of the day

or.. written the way round we would do it in a database:

sum up all the attempts, group ed by hour and find the maximum number in that list, then find all the hours having a sum equal to this maximum (this involves another sum operation), then find all the records that have that hour..

Am I correct?
 
Last edited:
Translating this into "cjard speak" I think I get:

For all site network ids, site names, cell network ids etc group by the hour regardless of the day, then find the hour with the highest attempt, then find a list of all sites and attempts in that hour, regardless of the day

or.. written the way round we would do it in a database:

sum up all the attempts, group ed by hour and find the maximum number in that list, then find all the hours having a sum equal to this maximum (this involves another sum operation), then find all the records that have that hour..

Am I correct?


The quoted spec gives rise to the following query:

VB.NET:
SELECT
  sites.* 
FROM 
  sites
  INNER JOIN
  (
    SELECT 
      hr 
    FROM 
      sites 
    GROUP BY 
      hr 
    HAVING 
      SUM(attempt) = (SELECT MAX(sum_att) FROM (SELECT SUM(attempt) as sum_att FROM sites GROUP BY hr) sum_att_finder) 
  ) hrs_of_max_sum
  ON
    sites.hr = hrs_of_max_sum.hr

Note that the test data you gave me did not have entries such that the max attempt summation was found at 11 hours. The max sum of attempts was found to be 9 attempts. Three hours all show 9 attempts in total. Those hours are 7, 10 and 15
 
What exactly is an Analytic Function. I have not heard of this. Can you give us a sample of what one would look like? Or a sample of some common Analytic Functions?
 
I use them most often to find the most recent record for a particular group e.g.

Our db has a customer, and they can have multiple addresses. To find the current address:

VB.NET:
SELECT * FROM
  customer
  INNER JOIN
  (SELECT address.*, ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY address_date DESC) as ranking FROM address) 
  USING(customer_id)
WHERE
  ranking = 1

All addresses are ranked in date order by the row number in that partition (customer id). the rank one line is the most recent
 
Back
Top