SQL Statement equivalent for Excel countif

Question

I have a column named 'cardnum' and another one named amount. (I actually have multiple other fields in the table but I'll stick with two for the example)

e.g.,

cardnum	amount
ABC123	50
ABC123	20
ABC123	30
ABC123	50
XYZ720	24
LFG475	35
LFG475	100
LFG475	0
ABC123	50
ABC123	20

Is there a SQL statement that returns a new column with the respective counts of each card number in the card number column?

It should look like this:

cardnum	amount	count
ABC123	50	6
ABC123	20	6
ABC123	30	6
ABC123	50	6
XYZ720	24	1
LFG475	35	3
LFG475	100	3
LFG475	0	3
ABC123	50	6
ABC123	20	6

I tried something like this (keep in mind I applied this to my entire table with 17 columns):

SELECT card_number, COUNT(*) as frequency
FROM your_table_name
GROUP BY card_number;

I also tried using COUNT(*) OVER(PARTITION BY...) but it just returns all the total row count for the entire new column.

but apparently you can't use groupby with * or all columns either have to be part of the groupby or included in an aggregate function.

Answer 1

Using window aggregation function:

SELECT *, COUNT(1) OVER (PARTITION BY cardnum) Count
FROM carddetails