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 ?
2条答案
按热度按时间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.
des4xlb02#
When asking a question it's really helpful to provide the DDL and DML for your demo data:
In this case it looks like a quick self join might be the way to go: