SQL Query on grouping

[Guest_imported]

Could anyone tell me how group parts of a field please?

eg if I had a list of salarys and wanted to group them 10-20k 20-40k and 40k+ how would I do this?

Thanks

 

[THoey]

First thing off the top of my head is a union query:

SELECT * FROM MyTable WHERE SALARY BETWEEN 10000 AND 20000
UNION
SELECT * FROM MyTable WHERE SALARY BETWEEN 20001 AND 40000
UNION
SELECT * FROM MyTable WHERE SALARY > 40000

But, since I haven't tried this, I am not 100% sure it would do it the way you want. Give it a try...
Terry M. Hoey

 

[pyttviper]

If you are counting the occurences in each range then this may be appropriateIt is union between 3 different queries


SELECT Count([salary]) AS [Number in Group], "10 to 20" AS [Group]
FROM salary
WHERE (((salary.salary) Between 10000 And 20000));
UNION
SELECT Count([salary]) AS [Number in Group], "20 to 40" AS [Group]
FROM salary
WHERE (((salary.salary) Between 20001 And 40000));
UNION
SELECT Count([salary]) AS [Number in Group], "40 +" AS [Group]
FROM salary
WHERE (((salary.salary) > 40000));

The table i used is structured like this

id Auton
name text
salary currency

HERE ARE THE CONTENTS

id name salary
1 man $10,000.00
2 joe $29,900.00
3 rick $22,222.00
4 steve $33,090.00
5 ron $49,990.00
6 jeff $13,000.00
7 linda $50,000.00

The query returns this result where type is the group type and number in Group is the number of salaries in that range


Number in Group Type
2 10 to 20
2 40 +
3 20 to 40


Hope this help
dan0

 

[tlbroadbent]

You can use the IIF function in a query.

Select IIf(Salary>=40000, "40k+", IIf([Salary]>=20000, "20-40k", IIf([salary]>=10000, "10-20k", "Under 10K&quot)) As Group, Count(EmployeeID) As EmpCount
From SalaryTable
Group By IIf(Salary>=40000, "40k+", IIf([Salary]>=20000, "20-40k", IIf([salary]>=10000, "10-20k", "Under 10K&quot)) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.