CODEWITHSUNDEEP
sql-serverjoinstored-proceduresinner-join
Richard
Richard

Reputation: 6116

SQL Count with Inner Join for Single Table

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?

Upvotes: 0

Views: 394

Answers (2)

Punith R Kashi
Punith R Kashi

Reputation: 149

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]

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

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

Upvotes: 3

Related Questions