0

I have two tables

  • tblData_VendorMasterSSPaymentTerms
  • tblData_VendorMasterSSPaymentTermsCLM

tblData_VendorMasterSSPaymentTerms contains a field labled VMSSPayTerms_AribaContractID which the values exist in table tblData_VendorMasterSSPaymentTermsCLM

So in table tblData_VendorMasterSSPaymentTermsCLM I want to create a calculated column that counts how many records in tblData_VendorMasterSSPaymentTerms contains the Contract ID for that record.

This is what I have put together so far but it is still coming up with an error

SELECT Count(VMSSPayTerms_AribaContractID)
From tblData_VendorMasterSSPaymentTerms
Where VMSSPayTerms_AribaContractID=VMSSPayTermsCLM_ContractID

Can someone help me identify what I am doing wrong here?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Shelton Thompson
  • 19
  • 3

1 Answers1

0

You must join the tables, group by VMSSPayTermsCLM_ContractID and count: 

select
  c.VMSSPayTermsCLM_ContractID,
  count(t.VMSSPayTerms_AribaContractID) counter
from tblData_VendorMasterSSPaymentTermsCLM c inner join tblData_VendorMasterSSPaymentTerms t
on t.VMSSPayTerms_AribaContractID = c.VMSSPayTermsCLM_ContractID
group by c.VMSSPayTermsCLM_ContractID
forpas
  • 160,666
  • 10
  • 38
  • 76
  • What exactly you mean by computed column? Do you want to alter the table and add a new computed column? This would need to create a function so to reference other table. – forpas Jul 18 '19 at 20:34