Re: Aggregating function which returns more than one row
No. It is not what I want. This is a normal aggregation. The sum function will be called once for each group (SalespersonNm) and it will return one row. What I have asked is : if I have 10 rows, can I return 12? It's rude, but that's the idea. Thanks, Felix DL Neil wrote: Felix, Is is it possible to write an aggregation function which returns more than one row? Yes - if I have understood your requirement correctly. It would have been helpful if you had given some idea of the application/what you want to achieve. Here's an example from out of my imagination: if there is a table holding details of all of the sales (invoiced lines) for a particular month's trading, and if each is recorded/credited against a particular salesperson. Let's say there are one million rows in the table, but only ten sales staff. You can ask MySQL to return the sum of (aggregate) all sales attributed to each salesperson, and to count the number of sales/lines for each person: SELECT SalespersonNm-or-ID, COUNT( * ) AS NumberOfSales, SUM( ExtendedValue) AS TValueOfSales FROM =tbl= WHERE =this-month= GROUP BY SalespersonNm This will analyse the million rows but only output ten lines of results/salespersons' names (and the NumberOfSales column's entries will add up to one million/the number of rows in the table). What you wanted? =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Aggregating function which returns more than one row
The idea is simple: I want to implement some temporal computation on timeseries like normalization and interpolation to smooth the data and fill the missing values. So, for example if have a table like that: starttime,endtime,counter 12:00,12:30,34 13:00,14:00,25 you see that for 12:00 the measurement was for 30 minutes and a segement is missing (12:30-13:00). What I want is to write some aggregation functions the will do this kind of computations like the following: select nisum(starttime,endtime,counter,interval,trashold) from my_table where That's it, Felix --- sql, query Victor Pendleton wrote: Felix, Can you please give an example of what you are attempting to do? -Original Message- From: Felix LUNGU [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 9:22 AM To: [EMAIL PROTECTED] Subject: Aggregating function which returns more than one row Is is it possible to write an aggregation function which returns more than one row? Thanks, felix Mysql sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Aggregating function which returns more than one row
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Felix LUNGU Sent: Thursday, December 05, 2002 12:27 AM To: Victor Pendleton; [EMAIL PROTECTED] Subject: Re: Aggregating function which returns more than one row The idea is simple: I want to implement some temporal computation on timeseries like normalization and interpolation to smooth the data and fill the missing values. So, for example if have a table like that: starttime,endtime,counter 12:00,12:30,34 13:00,14:00,25 you see that for 12:00 the measurement was for 30 minutes and a segement is missing (12:30-13:00). What I want is to write some aggregation functions the will do this kind of computations like the following: select nisum(starttime,endtime,counter,interval,trashold) from my_table where That's it, Felix --- sql, query Victor Pendleton wrote: Felix, Can you please give an example of what you are attempting to do? -Original Message- From: Felix LUNGU [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 9:22 AM To: [EMAIL PROTECTED] Subject: Aggregating function which returns more than one row Is is it possible to write an aggregation function which returns more than one row? Thanks, felix Mysql sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Aggregating function which returns more than one row
Felix, Is is it possible to write an aggregation function which returns more than one row? Yes - if I have understood your requirement correctly. It would have been helpful if you had given some idea of the application/what you want to achieve. Here's an example from out of my imagination: if there is a table holding details of all of the sales (invoiced lines) for a particular month's trading, and if each is recorded/credited against a particular salesperson. Let's say there are one million rows in the table, but only ten sales staff. You can ask MySQL to return the sum of (aggregate) all sales attributed to each salesperson, and to count the number of sales/lines for each person: SELECT SalespersonNm-or-ID, COUNT( * ) AS NumberOfSales, SUM( ExtendedValue) AS TValueOfSales FROM =tbl= WHERE =this-month= GROUP BY SalespersonNm This will analyse the million rows but only output ten lines of results/salespersons' names (and the NumberOfSales column's entries will add up to one million/the number of rows in the table). What you wanted? =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php