Anyone know how to do this?

[PruSQLer]

I have a query doing this:

Select product, count(*), sum(amount), sum(Portion)
From Event_Table et,
Money_Table mt
Where et.ID = mt.ID
and mt.Num in (1,500,510)
Group by product

My count gets inflated because an event can have more than one mt.Num associated with it, based on whether it's an amount or a portion. Is there a way to keep the count to the number of events, regardless of how many joins there are to the money table?

 

[fluteplr]

Select product, count(distinct et.id), sum(amount), sum(Portion)
From Event_Table et,
Money_Table mt
Where et.ID = mt.ID
and mt.Num in (1,500,510)
Group by product

I am assuming the et.id is the unique field in the et table, if not use one that is.

 

[PruSQLer]

That's a good tip. Thanks. Unfortunately, I simplified the query for the sake of brevity and it actually takes 3 fields (Pk_ID, PR_ID and Event_ID) to make the Event unique. I doubt that's possible with count (distinct).

 

[fluteplr]

Is Pk_ID, PR_ID and Event_ID really the primary key for the event table? If so what data type are the fields?
If so have you considered adding a single field primary key?


If they are character you could probably concatinate them.


Select product, count(Pk_ID, PR_ID and Event_ID ), sum(amount), sum(Portion)
From Event_Table et,
Money_Table mt
Where et.ID = mt.ID
and mt.Num in (1,500,510)
Group by product

if they are numeric you could try converting them to character fields and then concatinating them.