具有单个表的内部联接的SQL计数 [英] SQL Count with Inner Join for Single Table

问题描述

我有一个这样的表:

Name    Id      Amount 
Name1   1       99
Name1   1       30
Name1   9       120.2
Name2   21      348
Name2   21      21
Name3   41      99

我想选择每个名称，将其按ID分组，然后对交易进行计数(总和).所以我想要下表:

I want to select each name, group them by their id and count the transactions (NOT SUM). So I want the following table:

Name    Id      Count 
Name1   1       2
Name1   9       1
Name2   21      2
Name3   41      1

我尝试了此sql:

SELECT
    [Name],
    [Id]
FROM table1 A
INNER JOIN (
                SELECT
                [Id],
                count([Amount]) as 'Count'
                FROM 
                    table1
                GROUP BY [Id]
           )
B ON A.[Id] = B.[Id]

但是出现以下错误:Ambiguous column name 'Id'.

我在做什么错了?

推荐答案

SELECT
       [Name],
       [Id],
       count([Amount]) as 'Count'
FROM 
       table1
GROUP BY [Name], [Id]

这篇关于具有单个表的内部联接的SQL计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！