counting fields

[mcchung52]

Hello all,
I'm currently working with a long list of records containing people's names
and their affliated facilities.

field1 contains facility name
field8 contains people name
field15 contains city where they live in


in the list, each individual will have different products serviced
e.g
field1 ... field8 ... field15
=== === ====
A smith product1 New York
A smith product2 New York
A ronald product1 New Jersey
B cheng product3 Boston

I have a following query to pull a report
where I want to count how many products each individual
has taken.

SELECT DISTINCT field1, field8, count(field8), field15
FROM sheet1
GROUP BY field8
ORDER BY field15, field1;

Am I doing something wrong? I can't even get it to show.
Any pointer will be appreciated. Thanks.

[June7]

You don't include the product field in the query. How are the facility and city relevant if you want count of products per individual?

SELECT field8, Count(product) As ProdCt FROM sheet1 GROUP BY field8;

Alternatively, don't do aggregate calcs in query. Build report and use it's grouping & sorting with aggregate calcs functionality. This allows display of detail records and summary data in headers/footers.

[mcchung52]

I sort of achieved what I want with the following query:

SELECT DISTINCT field1, field8, count(field8), field15
FROM sheet1
GROUP BY field1, field8, field15
ORDER BY field15, field1;

field15, city, is just city not really relevant in terms of how I want to combine data.
just realized you need to include all fields in the SELECT in the GROUP BY section. Is this correct?

-"Alternatively, don't do aggregate calcs in query. Build report and use it's grouping & sorting with aggregate calcs functionality."
Can you elaborate what you mean here? Thanks.