Translating Excel's COUNTIF function to SQL for counting repeated records

Data and Databases
1

I’m trying to move my data from Excel to a database, but I’m stuck on one part. In Excel, I use a formula to count how many times a registration number shows up. It’s in a column called ‘Count’.

Here’s what it does:

  • When I add a new record, it updates the count for that registration number
  • For example, if I add a new record with reg_no 3591, the count becomes 4

I’m not sure how to do this in SQL. Can anyone help me figure out how to recreate this counting feature in a database query? I’ve tried a few things, but nothing seems to work quite right.

Here’s a simple example of what my data looks like:

CREATE TABLE students (
  id INT,
  reg_no INT,
  name VARCHAR(50),
  grade CHAR(1),
  count INT
);

INSERT INTO students VALUES
(1, 3591, 'Alice', 'B', NULL),
(2, 3592, 'Bob', 'A', NULL),
(3, 3591, 'Charlie', 'C', NULL);

How can I update the ‘count’ column to show the number of times each reg_no appears?

2

yo, i get what ur trying to do. have u looked into window functions? smth like this might work:

SELECT , COUNT() OVER (PARTITION BY reg_no) AS count
FROM students

it’ll give u a running count for each reg_no. lmk if that helps!

3

hmm, that’s a neat idea. have you tried using this subquery approach? update students s set count = (select count(*) from students s2 where s2.reg_no = s.reg_no) might work. what else have you tinkered with?

4

To replicate Excel’s COUNTIF functionality in SQL, you could use a correlated subquery in an UPDATE statement. This approach allows you to update the ‘count’ column for all rows simultaneously:

UPDATE students s
SET count = (
SELECT COUNT(*)
FROM students s2
WHERE s2.reg_no = s.reg_no
);

This query counts the occurrences of each reg_no and updates the corresponding ‘count’ column. It’s efficient for smaller datasets, but for large tables, consider creating an index on reg_no to improve performance. Remember to run this update whenever you insert new records to keep the counts current.