How to add row numbering to a SELECT aggregate query

[metaltree]

I have the following table:

id group item qty
1 fruits banana 2
2 fruits orange 3
3 fruits grape 5
4 b+c bread 10
5 b+c noodles 12
6 meat eggs 7

The following SQL statement:
SELECT food_list.group, Count(food_list.item) AS nb_items, Avg(food_list.qty) AS avg_qty
FROM food_list GROUP BY food_list.group;

returns:
group nb_items avg_qty
b+c 2 11
fruits 3 3,333333333
meat 1 7

How can I add row numbering in my output table, using the SELECT statement, since this is an aggregate function?

 

[AlexCuse]

what database?

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[metaltree]

Access or PostgreSQL.

 

[elsenorjose]

Try this (of course, change your code accordingly) :

SELECT CustCode, CustName, 
(SELECT COUNT(*) FROM Customers C2 WHERE C2.CustCode <= C.CustCode) AS SrNo
FROM Customers C
ORDER BY CustCode

 

[AlexCuse]

Yup, I think that's going to be your only choice :-(

I saw PostgreSQL has something called a 'temp sequence' that you can add, but I don't really know enough about this to comment. It can't be much worse than the count(*) approach, may be worth doing a few searches for though...


[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!