Counting Occurrences in a Table

Question

Hello everyone and thanks for the help in advance. I need to develop a MVC application that reads a SQL database of patients who have received vaccines. The table looks like:

	[ID] [int] IDENTITY(1,1) NOT NULL,
	[MRNumber] [varchar](50) NULL,
	[PatientFirstName] [varchar](50) NULL,
	[PatientLastName] [varchar](50) NULL,
	[PatientDOB] [varchar](50) NULL,
	[Vaccine] [varchar](500) NULL,

Each individual vaccine given is a row in the table, in other words, measles, polio, varicella, etc. I need to generate a report that analyzes, by patient (MRNumber) the number of each of the vaccines they have received, for example 3 measles, 2 polio, 2 varicella. So I am trying to figure out the best way to approach this. I am not sure if there is any way to do this with transact sql. My other idea was to sort by MRNumber, then loop through each row of data, creating a separate ListOf model to tally the totals. However, this seems like a really resource intensive method. Any help would be appreciated.

Answer 1

Before we start to look at a query, we need to look at the table design, because there are, well, issues.

To start with, all columns except for the identity column is nullable. That begs the question what does a row with only an MRNumber imply? That a person was vaccinated, but we don't know against what? And we don't know what the name person had at the time? Or what if only Vaccine has a non-null value what does that mean? That someone was vaccinated for, say, measels, but we don't know whom it was? And what would a row with only NULLs imply? Someone was vaccinated, but we don't know whom and for what?

Generally, when you permit a column to be nullable, you need to have an understanding of what NULL in that column would mean, and how you are going to handle it. If you don't, you should probably not permit NULL values.

Furthermore, if MRNumber identifies the person, why storing the patients names and DOB in the table? Do we need to record that the person had different names on different occasions? That could certainly happen, but the date of birth cannot change. I would expect this data to appear in a table where MRNumber is the key.

On the other hand, I would expect the table also include the date when the vaccine was given. Also, I would not expect Vaccine to hold the actual disease, but something that identifies the vaccine as such, and another table would hold data about the vaccine, and a child table to that table would hold the diseases it is good for.

Another problem with your table is that if Kate takes a shot against covid-19, and someone mistakenly enters this twice, you have false information.

These considerations leads to a table with three columns: MRNumber, VaccineID and VaccinationDate with all three as key columns. If you also want a surrogate key for some reason, you can slap it on. But in my experience, such keys can cause your more grief and benefit in the long run.

Given this table, the query you would run to get the vaccines would be

SELECT MRNumber, VaccineID, COUNT(*)
FROM  Vaccinations
GROUP  BY MRNumber, VaccineID

If you want to know the diseases, the query would be:

SELECT V.MRNumber, VD.Disease, COUNT(*)
FROM  Vaccinations V
JOIN   VaccineDiseases VD ON V.VaccineID = VD.VaccineID
GROUP BY V.MRNumber, VD.Disease