SQL Server How to check same value of one column is present in other column multiple times or not

4ioopgfo  于 12个月前  发布在  其他
关注(0)|答案(2)|浏览(122)

I am using SQL Server with table name = Immerse which contains below records:

ProjID  |   GroupId
--------+--------------------------
  1     |    AAA
  2     |    BBB
  3     |    AAA
  4     |    CCC
  4     |    CCC
  4     |    CCC

and I would like to know whether same GroupID is associated with multiple ProjID are not? and display the count

Output would be

GroupId  | Count
---------+-------
 AAA     |    2
 BBB     |    1
 CCC     |    1

Note: Here GId = 'CCC' is associated to only 1 ProjId i.e; '4' so count should be equal to 1 only

and I have written query like this:

SELECT
    GId, COUNT(GId) AS Count 
FROM
    Immerse 
GROUP BY
    GId, PId

which produces output as

GroupId  | Count
---------+---------
 AAA     |    1
 BBB     |    1
 AAA     |    1
 CCC     |    1

Do I need to use having clause after group by clause ?

zynd9foi

zynd9foi1#

You can count the distinct ProjIds, and then group by GroupId. In other words this query only counts each GroupId+ProjId combination once, no matter how many times the combination appears.

SELECT
        GroupID, 
        COUNT(DISTINCT ProjId)
    FROM
        Immerse
    GROUP BY GroupID
des4xlb0

des4xlb02#

When asking a question it's really helpful to provide the DDL and DML for your demo data:

DECLARE @Immerse TABLE (ProjID INT, GroupID VARCHAR(3));
INSERT INTO @Immerse (ProjID, GroupId) VALUES
(1, 'AAA'), (2, 'BBB'), (3, 'AAA'), (4, 'CCC'), (4, 'CCC'), (4, 'CCC');

In this case it looks like a quick self join might be the way to go:

SELECT i1.GroupID, COUNT(i2.GroupID) AS NumberOfProjects, STRING_AGG(i2.ProjID,', ') AS OtherProjects
  FROM @Immerse i1
    LEFT OUTER JOIN @Immerse i2
      ON i1.GroupID = i2.GroupID
      AND i1.ProjID <> i2.ProjID
GROUP BY i1.GroupID;
GroupIDNumberOfProjectsOtherProjects
AAA21, 3
BBB0
CCC0

相关问题