SQL Count with Inner Join for Single Table

Question

I have a table like this:

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

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

I tried this 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]

But I get the following error: Ambiguous column name 'Id'.

What am I doing wrong?

See the answer below from @JuanCarlosOropeza. But the reason you were getting that error is because in your subquery it is impossible to know which table you want Id from. Remember that at point the subquery AND the main query are both in scope. You also need to correlate that subquery to the main query,. — Sean Lange, Feb 15 '17 at 22:29

score 3 · Accepted Answer · answered Feb 15 '17 at 22:24

3

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

answered Feb 15 '17 at 22:24

Juan Carlos Oropeza

47,252
12
78
118

Perfect, so simple! – Richard Feb 16 '17 at 15:33

score 0 · Answer 2 · answered Feb 15 '17 at 22:24

0

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

answered Feb 15 '17 at 22:24

Punith R Kashi

139
1
10

@SeanLange Isnt join by `[ID]` ? – Juan Carlos Oropeza Feb 16 '17 at 13:47

SQL Count with Inner Join for Single Table

2 Answers2

Linked