I have one table:

So lots of duplicates as you can see. I need to basically write a query that does 2 things:

1. Matches the separate IDs with linked accounts -- so 1, 3 are 22
2. Counts the number of times they link -- so in the case above, twice.

Edit:

My desired result is the number of times Account 22 is linked with an ID and the list of those IDs.

CodePudding user response：

Tested: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6d84edba642dcb057441ea15540e8da4

If you have an analytic function available a simple group by, and count with the analytic count()

SELECT ID, Account, Count(ID) over (Partition by Account) as CNT
FROM OneTable
GROUP BY ID, Account
ORDER BY Account, ID

The analytic basically says, For each account return a count of the distinct IDs. the outer group by returns a list of ID's and accounts

So results would look something like: (Assuming I did it right)

ID  Account CNT
1   22      2
3   22      2
2   44      1


DDL Sample: SOURCE
SELECT ID, Account, Count(ID) over (Partition by Account) as CNT
FROM (Values (1 , 22),
(1, 22),
(2, 44),
(3, 22)) X (ID, Account)
GROUP BY ID, Account
ORDER BY Account, ID

CodePudding user response：

This can be solved with a "group-by", "count()" (aggregat-function), an "alias" to the same table and a "sub-query":

• group-by: https://www.w3schools.com/sql/sql_groupby.asp
• count(): https://www.w3schools.com/sql/sql_count_avg_sum.asp
• alias: https://www.w3schools.com/sql/sql_alias.asp

Something like this should work:

SELECT
  master.ACCOUNT,
  master.COUNT(*) as number
FROM
  table master
WHERE distinct ID in
(
   SELECT
     distinct ID
   FROM
     table t1,
     talbe t2
   WHERE
     t1.account = t2.account
)
GROUP-BY
  master.ACCOUNT 
;

SQL-Result:

For the IDs you would have to use something equivalent to Oracle "ListAGG" - but that depends on the database you are using. → It is best to follow these instructions:

ListAGG in SQLSERVER

Also take care of dupplicates in a table, this should generally be avoided → Maybe you have a auto-increment primary key (pk) for that table - something like this:

If you need to update the same ID, use a "MERGE" (update or insert) - The syntax for a "merge"-query also depends on the used database (e.g. Oracle SQL Server ...).