find High, Low & Median for a set of values based on first 3 char

[mckenneyj]

I have a table x with 2 fields Case and Amount
27,000 records

Table is formatted as
Case Amount
AAA2005-00000 200.00
AAA2005-00001 100.00
AAA2005-00005 1000.00
AAA2005-00010 500.00
BBB2005-00000 100.00
BBB2005-00002 25.00
CCC2005-00000 500.00

I need to group my calculations and results by the first 3 charcaters of Case.
I then need to return
1.) Sum of all records where first 3 characters match
2.) High Value of matching records
3.) Low Value of Matching records
4.) Average Value of Matching records

in the example above I would return
AAA 1800 1000 100 450
BBB 125 100 25 62.50
CCC 500 500 500 500






Thanks,
John McKenney
Work Hard... Play Harder

 

[PHV]

Something like this ?
SELECT Left(Case,3) As Case3, Sum(Amount) As Total, Max(Amount) As HighValue, Min(Amount) As LowValue, Avg(Amount) As AverageValue
FROM x
GROUP BY Left(Case,3)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mckenneyj]

Thanks
Post is now closed

Thanks,
John McKenney
Work Hard... Play Harder