Find the count of unique FK associated with two or more Col2 values



  • I’m trying to find a way to get a count of unique foreign keys that are associated with at least two different Col2 values across multiple rows. Example:

    PK Col2 FK
    A page1 abc
    B page2 abc
    C page1 fgd
    D page2 mnl

    I want to find the count of unique FK associated with BOTH Page1 and Page2 for a large amount of data. Any advice on the most efficient way to do this?

    Edit: thanks for the responses and follow up. I will try to clarify: the desired result of the query I need for this table would be 1 (the number of unique values of FK that are associated with both page1 and page2). The question I’m looking to answer is essentially how many users hit page1 and also hit page2 in the same session. Each hit is a unique key under PK and whether they belong to the same session is determined by the key under FK.



  • You can use a window function for this, no self-joins are needed.

    SELECT *,
      COUNT(*) OVER (PARTITION BY FK)
    FROM YourTable;
    

    https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7cb4e363a018d44faf05433f126fb6ff

    For efficiency you will want an index with FK as the leading key column.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2