Distinct count in a query

[Fabdav]

I have a query I have 4 fields:
E.G.
Field1 Field2 Field3 Field4
C 20 25 145541
C 40 20 145541
C 10 10 145540
D 20 20 145540
D 10 10 145538




I need to obtain:

C 70 55 2
D 30 30 2


I need to group Field1 and sum Field 3 and field3 and count the UNIQUE ID in field 4.

Any suggestion?

[Robeen]

Do you mean:

I need to obtain:

C 70 55 3
D 30 30 2

?

[Robeen]

Do you mean:

I need to group Field1 and sum Field 3 and field4 and count the UNIQUE ID in field 4.



I think you're on the right track.
Group by 1, Sum 2 & 3 & Count 4.

[TinaCa]

Create the query as a select -make sure it runs - sounds like you got that far .

Next select the totals button in the query creator (likes like a grate big E kind of). This will put the Total field in the grid of the query. In the first field select group from the drop down, then select sum for the second and third and count for the last one.

[kennejd]

Group by 1, Sum 2 & 3 & Count 4

It's not quite that easy. You're going to have to create a separate query to group by Field1 and Field4. Another query to group and sum Fields 1,2, & 3. Then a final query to join the first 2 queries on Field 1....group by Fields 1,2, & 3 and count field 4.

You could probably write some sql and use subqueries, but I think the 3 separate queries is easier....although my description doesn't make it sound very easy!

[alansidman]

Congrats Kennejd;
I have attached for OP to view and understand your explanation. Great solution as I was fumbling with this one for a good answer.