如何根据两列查找一列的计数

How I can get last column based on the "competition" and "user" columns? the following query gives error!

我如何根据“竞争”和“用户”列获得最后一栏?以下查询给出错误!

SELECT DISTINCT COUNT(*) AS countofcomments
FROM k
GROUP BY competition, user

2 个解决方案

#1

I don't know is that a good practice you trying to achieve, On considering the result set you can try something like this

我不知道这是你想要实现的好习惯,在考虑结果集时你可以尝试这样的事情

CREATE TABLE #Example(
       [competition]      [nvarchar](50)   NULL,       
       [user]   [nvarchar](50)   NULL,
       [comments]   nvarchar(50) NOT NULL,
)
GO

INSERT   #Example  ([competition],[user],  [comments])   VALUES ('INDIA','CHENNAI','ssss')
INSERT   #Example  ([competition],[user],  [comments])   VALUES ('INDIA','KOCHI','ssss')
INSERT   #Example  ([competition],[user],  [comments])   VALUES ('INDIA','BANGLORE','ssss')
INSERT   #Example  ([competition],[user],  [comments])   VALUES ('INDIA','HYDERABAD','ssss')
INSERT   #Example  ([competition],[user],  [comments])   VALUES ('US','MAIAMI','ssss')
INSERT   #Example  ([competition],[user],  [comments])   VALUES ('US','SANFRANC','ssss')
INSERT   #Example  ([competition],[user],  [comments])   VALUES ('US','MOUNT','ssss')
INSERT   #Example  ([competition],[user],  [comments])   VALUES ('US','LOSANGELS','ssss')
INSERT   #Example  ([competition],[user],  [comments])   VALUES ('UK','MANCHESTER','ssss')
INSERT   #Example  ([competition],[user],  [comments])   VALUES ('UK','CHELSEA','ssss')

SELECT * from (
SELECT * FROM #Example 
) tab1 join 

(
SELECT [competition] , count([comments] )count FROM #Example
group by [competition]
) tab2 on tab1.[competition]= tab2.[competition]


DROP table #Example

#2

You can do this using window function:

您可以使用窗口功能执行此操作:

SELECT *, COUNT(*) OVER(PARTITION BY competition, user) as [Count of comments]
FROM k

#1