SQL - Counting Rows Per Value

Question

I have a table of Accounts, each with a unique ID. Then I have a table of Transactions, each with a unique ID. Each row of the Transactions table has an Account ID, to show which particular Account it goes with. So there can be multiple rows in the Transactions table with the same Account ID, each pointing to the same Account, to indicate that the Account had multiple transactions.

Each Account can have any number (0-n) transactions.

I'm joining the Transactions table to the Account table fine, with the output showing a row for each Transaction (so there are multiple rows for some Account numbers) or, if there are no Transactions, showing null in the transaction fields:

|Account ID|Account Name|Transaction ID|Transaction Name|
---------------------------------------------------------
|acc1      |Account 1   |tran1         |Transaction 1   |
|acc1      |Account 1   |tran2         |Transaction 2   |
|acc1      |Account 1   |tran3         |Transaction 3   |
|acc2      |Account 2   |tran4         |Transaction 4   |
|acc3      |Account 3   |null          |null            |
|acc4      |Account 4   |tran5         |Transaction 5   |
|acc4      |Account 4   |tran6         |Transaction 6   |

But what I want is to get a count for the number of transactions - I don't care about any information related to each transaction; I just want to get a count of the number of transactions for each account:

|Account ID|Account Name|Number of Transactions|
------------------------------------------------
|acc1      |Account 1   |           3          |
|acc2      |Account 2   |           1          |
|acc3      |Account 3   |           0          |
|acc4      |Account 4   |           2          |

How can I get this count?

FWIW - Microsoft SQL Server

Answer 1

DECLARE @Account TABLE(AccountID INT, AccountName NVARCHAR(50))
DECLARE @Transaction TABLE(AccountID INT, TransactionID INT)
INSERT INTO @Account
VALUES(1,'Acount 1'),
(2,'Acount 2'),
(3,'Acount 3')

INSERT INTO @Transaction
VALUES(1, 1),
(1, 2),
(1, 3),
(2, 4),
(2, 5)

SELECT a.AccountID, a.AccountName, Count(t.TransactionID) AS 'Number Of Transactions' FROM @Account a
LEFT JOIN @Transaction t ON a.AccountID = t.AccountID
Group By a.AccountID, a.AccountName

Answer 2

Assuming your table structure is this

create table tran (accid varchar, accname varchar, tranid varchar, tranname varchar)

you can simply achieve this using group by clause

select accid,accname, count(tranid) from tran group by accid