CODEWITHSUNDEEP
sqlsql-serversql-server-2012
mprobus
mprobus

Reputation: 15

Count the Number of Times a value is in a Column (With additional fields)

I have the below query to look for all entries that exist more than 1 time.

      SELECT p.Caption, COUNT(o.NodeID) as Count
      FROM
          NodesData p INNER JOIN
          Interfaces o ON o.NodeID = p.NodeID

      GROUP BY p.Caption, o.Caption

      HAVING Count(o.NodeID) > '1'

The query works fine. However, I would like to add additional data to the Select. When doing this, it breaks the query because all of the counts are 1. How do I get the query to still do an accurate count while allowing view of the additional data. See below for example.

      SELECT p.Caption, o.Caption, COUNT(o.NodeID) as Count
      FROM
          NodesData p INNER JOIN
          Interfaces o ON o.NodeID = p.NodeID

      GROUP BY p.Caption, o.Caption

      HAVING Count(o.NodeID) > '1'

Upvotes: 0

Views: 42

Answers (2)

Matt
Matt

Reputation: 14341

;WITH cte AS (
    SELECT p.Caption, o.Caption as InterfaceCaption, COUNT(o.NodeId) OVER (PARTITION BY p.Caption) as NodeCount
    FROM
       NodesData p
       INNER JOIN Interfaces o
       ON o.NodeId = p.NodeId
)

SELECT *
FROM
    cte
WHERE
    NodeCount > 1

Use COUNT() OVER window function in a Common Table Expression (or derived table) and then add your where clause. Otherwise you would have to take your result of your first query and join back to your tables to retrieve the other details you want.

you can also use the join back to the original tables method but its more verbose in my opinion:

;WITH cte AS (
    SELECT p.Caption, COUNT(o.NodeID) as Count
    FROM
       NodesData p INNER JOIN
       Interfaces o ON o.NodeID = p.NodeID
    GROUP BY p.Caption, o.Caption
    HAVING Count(o.NodeID) > '1'
)

SELECT
    *
FROM
    cte c
    INNER JOIN NodesData p
    ON c.Caption = p.Caption
    INNER JOIN Interfaces o
    ON p.NodeId = o.NodeId

Upvotes: 1

Alex
Alex

Reputation: 2335

You are probably getting ones because o.Caption is different on every row on Interfaces, you could trick o.Caption by doing a MAX on it like the following:

Fiddle

Upvotes: 0

Related Questions