question

timmins avatar image
timmins asked

Counts across different tables producing strange results

Hi I am trying to join a number of tables with differnt clientIDs to work out how many are assigned to each cluster. The cluster is contained in table26 and not every ClientID has a cluster. Table1 contains all of the unique ClientIDs i was expecting to group on the cluster & just add the tables & counts until I had gone through the list but by adding each extra tables the numbers change from the original total the first query produces different results for count(t26.clientID) to the second query, the first one returns accurate reults. Not all ClientIDs are in each table so I am doing left outer joins & joining on ClientID with the cluster table this is driving me nuts select t26.cluster, COUNT(t26.ClientID) ClusterCount--, --COUNT(t9.clientid) HBC from table1 t1 left outer join Table26 t26 on t1.clientID=t26.ClientID --left outer join Table9 t9 on t9.clientid=t1.ClientID group by t26.Cluster select t26.cluster, COUNT(t26.ClientID) ClusterCount, COUNT(t8.clientid) HBC from table1 t1 left outer join Table26 t26 on t26.clientID=t1.ClientID left outer join Table8 t8 on t8.clientid=t1.ClientID group by t26.Cluster
countnullouter-join
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
timmins avatar image
timmins answered
Just writing the problem out led me to the answer there are varios duplictes in the tables & because they were in the join columns this created the problem
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.