Need to select top 5 rows

[AJ]

I want to set up a select to get the sum of a value but I only want to sum up
the first 5 rows.

Example:

select sum(x)
from tableA
where **ROWCOUNT <= 5
order by y

Is this possible in VBA/Access?? Example? Thanks

 

[KARL DEWEY]

Try this ---
select TOP 5 sum(x)
from tableA
order by y;

 

[AJ]

Thanks Karl - If I do that I get the error "you tried to execute a query that
does not include the specified expression 'Y' as part of an aggregate
function. Any ideas?

 

[KARL DEWEY]

Try this ---
select TOP 5 sum(x), y
from tableA
order by y;

 

[AJ]

Damn....no same error.

Try this ---
select TOP 5 sum(x), y
from tableA
order by y;

 

[Allen Browne]

Damn....no same error.

Sum(x) without any GROUP BY will yield only one record.
Which Y do you expect to see with that total?

GROUP BY y.
Or perhaps try First(y)

(It also helps to explicitly alias aggregated fields.)

 

[AJ]

Hello Allen: I understand that sum will only return one record and I want to
only sum the first five rows, based on column Y.
Can this be done?

 

[Allen Browne]

Perhaps something like this:

SELECT Sum(x) AS SumOfX
FROM Table1
WHERE Table1.ID IN
(SELECT TOP 5 ID
FROM Table1 AS Dupe
ORDER BY Dupe.y, Dupe.ID);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html