5 messages in com.mysql.lists.mysqlRe: Aggregating function which return...
FromSent OnAttachments
Felix LUNGU04 Dec 2002 07:21 
DL Neil04 Dec 2002 10:42 
Felix LUNGU04 Dec 2002 23:26 
Felix LUNGU04 Dec 2002 23:34 
Grant Cooper05 Dec 2002 14:00 
Subject:Re: Aggregating function which returns more than one row
From:Felix LUNGU (feli@alcatel.ro)
Date:12/04/2002 11:34:32 PM
List:com.mysql.lists.mysql

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