Counting occurrences of Record ID number for use in a calculated column
In my SQL Server db, I have a field that contains a record ID that is repeated on each line of a transaction. Another field contains total cost for the associated transaction that is also repeated. Within the Excel file from which the data is imported, I can use the following equation to create a new column from which I can then use SUMIFS to get the total cost (SUMIFS on the original yields total costs * the number of lines in which the amounts are repeated). The Excel equation is =(SUMIFS(Cost column, ID column, ID number)/countifs(ID column,ID number))*(1/countifs(ID column,ID number). Here is a sample from Excel Equation =(SUMIFS(Q:Q,P:P,P7)/COUNTIFS(P:P,P7))*(1/COUNTIFS(P:P,P7)) ID Number Cost Calc Column Trx Total Proof 12345 1,250.00 416.67 1250 - 12345 1,250.00 416.67 1250 - 12345 1,250.00 416.67 1250 - 67891 1,422.00 284.40 1422 - 67891 1,422.00 284.40 1422 - 67891 1,422.00 284.40 1422 - 67891 1,422.00 284.40 1422 - 67891 1,422.00 284.40 1422 - I would like to add a calculated column to the table that accomplishes the same thing. Each row would then produce a fraction of the total that will add up to the correct amount when aggregated at the ID number level. Is there a way to pass the number of times a field includes a particular result (ID number), to the calculation?