CODEWITHSUNDEEP
sqlsql-serverjoingroup-by
tayopi
tayopi

Reputation: 313

How can I use an INNER JOIN in conjunction with a GROUP BY clause?

I have two tables, Transaction and TransactionType. Here is a sample of what the tables look like. 

Transanction Table
+---------------+------------+--------+-------------------+
| TransactionId | CustomerId | Amount | TransactionTypeId |
+---------------+------------+--------+-------------------+
|             1 |       2743 | 21.43  |                 3 |
|             2 |       6185 | 81.38  |                 3 |
|             3 |       3157 | 32.76  |                 2 |
|             4 |       9435 | 91.75  |                 1 |
|             5 |       1853 | 17.09  |                 3 |
+---------------+------------+--------+-------------------+

TransactionType Table
+-------------------+-------------+
| TransactionTypeId | Description |
+-------------------+-------------+
|                 1 | Cash        |
|                 2 | Card        |
|                 3 | Check       |
+-------------------+-------------+

I am looking for a way that I can show the number of occurrences of each TransactionTypeId in the Transaction table and then JOIN it to the TransactionType table so that I can see the description. I tried to use this SELECT statment:

SELECT COUNT(t.TransactionTypeId), tt.[Description]
FROM TransactionTable t
INNER JOIN TransactionType tt ON t.TransactionTypeId = tt.TransactionTypeId
GROUP BY t.TransactionTypeId
ORDER BY tt.[Description]

Hoping to create this result: 

+----------------------------+-------------+
| COUNT(t.TransactionTypeId) | Description |
+----------------------------+-------------+
|                          3 | Card        |
|                          1 | Cash        |
|                          1 | Check       |
+----------------------------+-------------+

However, I got an error saying, "Column 'TransactionType.Description' is invalid in the select list because it is not contained in either an aggregate function of the GROUP BY clause."

What am I doing wrong. How do I need to change my SELECT statement to achieve my desired result?

Upvotes: 5

Views: 3732

Answers (2)

neeohw
neeohw

Reputation: 605

I'm assuming you want this: the number of records per TransactionTypeId. So you're grouping the wrong column. You want to group by TransactionTypeId and then count the number of records (f.i by counting the TransactionId's)

This query should work:

SELECT COUNT(t.TransactionId), tt.[Description]
FROM TransactionTable t
INNER JOIN TransactionType tt ON t.TransactionTypeId = tt.TransactionTypeId
GROUP BY tt.[Description]
ORDER BY tt.[Description]

Upvotes: 2

unknown
unknown

Reputation: 461

You should group by tt.Description

Group by returns a relation/table with a row for each group, This error is saying, if you are going to use the GROUP BY clause, so in your SELECT statement you can only select the column that you are grouping by and use aggregate functions on that column because the other columns will not appear in the resulting table.

SELECT COUNT(t.TransactionTypeId), tt.[Description]
FROM TransactionTable t
INNER JOIN TransactionType tt ON t.TransactionTypeId = tt.TransactionTypeId
GROUP BY tt.[Description]
ORDER BY tt.[Description]

Upvotes: 4

Related Questions