Is it possible to achieve the following?

Select ID, Type, Sum(Amount)Amount
From MyTable
Where Type = 'XY'
GroupBy ID

If the number (count) of records is between 5 and 10 the query will return
one record with the sum of Amount.
If the number of records is less than 5 and more the 10 the query will be
blank.

Regards
Petter Topp

"Petter Topp" <petter.topp@atcdata.no> wrote in message
news:4A2139AD-20D2-48F3-9BFE-AAE9E370D044@news.elevatesoft.com...
> Is it possible to achieve the following?
>
> Select ID, Type, Sum(Amount)Amount
> From MyTable
> Where Type = 'XY'
> GroupBy ID
>
> If the number (count) of records is between 5 and 10 the query will return
> one record with the sum of Amount.
> If the number of records is less than 5 and more the 10 the query will be
> blank.
>

Select ID, Type, Sum(Amount)Amount, sum(1) mycount
From MyTable
Where Type = 'XY'
GroupBy ID
having (mycount > 4) and (mycount < 11)

Robert

Petter


>Is it possible to achieve the following?
>
>Select ID, Type, Sum(Amount)Amount
>From MyTable
>Where Type = 'XY'
>GroupBy ID

Probably not directly but you could use a script along the lines of

Select ID, Type, COUNT(1) AS Decider, Sum(Amount)Amount INTO "Memory\Temp"
From MyTable
Where Type = 'XY'
GroupBy ID;

DELETE FROM "Memory\Temp" WHERE Decider < 5 OR Decider > 10;

Roy Lambert [Team Elevate]

Hello Robert.

Your help is very much apreciated, and the solution is just excellent.

Regards
Petter



"Robert" <ngsemail2005withoutthis@yahoo.com.ar> skrev i melding
news:B70BAE8B-6EFE-40E4-8D58-F137847606FA@news.elevatesoft.com...
>
> "Petter Topp" <petter.topp@atcdata.no> wrote in message
> news:4A2139AD-20D2-48F3-9BFE-AAE9E370D044@news.elevatesoft.com...
>> Is it possible to achieve the following?
>>
>> Select ID, Type, Sum(Amount)Amount
>> From MyTable
>> Where Type = 'XY'
>> GroupBy ID
>>
>> If the number (count) of records is between 5 and 10 the query will
>> return one record with the sum of Amount.
>> If the number of records is less than 5 and more the 10 the query will be
>> blank.
>>
>
> Select ID, Type, Sum(Amount)Amount, sum(1) mycount
> From MyTable
> Where Type = 'XY'
> GroupBy ID
> having (mycount > 4) and (mycount < 11)
>
> Robert
>
>
>
>