CODEWITHSUNDEEP
sqlvb.net
Chet Horton
Chet Horton

Reputation: 33

VB.NET\Query - COUNT / GROUP BY QUERY PRODUCES DIFFERENT RESULTS

I got a query from a question I asked earlier. It worked perfectly. I wanted to count the total number of entries for each student and display their total in a datagridview.

So Jason Smith attended 2 different days which are 2 different entries by date in my table and the datagridview displays 2 for Jason's total classes. David Harris attended 1 class and the datagridview displays 1 for David's total classes.

Here is the query that worked.

SELECT
        FirstName AS [First Name],
        LastName AS [Last Name],
        TrainDate AS [Training Date], Count(*) AS TotalCount
    FROM 
        ACJATTENDANCE    
    GROUP BY FirstName, LastName, TrainDate, TrainDate

In the query that didn't work it display all the dates attended for each person and a 1 for total class on each date in the datagridview.

SELECT
                          ID AS [STUDENT ID],
                          FirstName AS [First Name],
                          LastName AS [Last Name],
                          TrainDate AS [Training Date], Count(*) AS TotalCount  
                     FROM 
                           ACJATTENDANCE
                     GROUP BY ID, FirstName, LastName, TrainDate, TrainDate

Why didn't my modification work the same way?

Upvotes: 0

Views: 247

Answers (1)

Caius Jard
Caius Jard

Reputation: 74660

Because you group by ID, which (if it's a proper ID) is unique, meaning that every group will have a size of 1

Let's look at an artificial example:

Animals
ID, Type
1, Cat
2, Cat
3, Cat
4, Cat
5, Dog
6, Dog

4 cats, 2 dogs.

SELECT Type, COUNT(*) FROM Animals GROUP BY Type

Cat, 4
Dog, 2

In your mind, imagine a bucket with "Cat" written on and another with "Dog" written on. Write the rows out on individual sheets of paper and put them into the buckets. That's what the grouping does; you'll end up with 4 sheets of paper in the cat bucket and 2 in the dog bucket. There is one bucket per unique value: cat is one value, dog is another = 2 buckets

Now if you group by ID, you need one bucket for every unique value. That's 6 buckets, numbered 1 to 6, with one piece of paper in each

If you have multiple clauses in a group by, you end up with as many buckets as there are combinations of unique values:

Animals
ID, Type, Neutered/Spayed
1, Cat, Yes
2, Cat, No
3, Cat, Yes
4, Cat, No
5, Dog, Yes
6, Dog, Yes

SELECT Type, Neutered, COUNT(*) GROUP BY Type, Neutered

Cat, Yes, 2
Cat, No, 2
Dog, Yes, 2

GROUP BY has generated 3 buckets this time with 2 papers in each, because some cats have been neutered and some have not, but all the dogs have been neutered. There is no "Dog/No" grouping because none of the rows have this combination.

Thus, the number of groups you get is the number of distinct combinations of the column data specified in the group by. When you specify an ID, which is unique for every row, it doesn't matter what other things you group by - you will always only ever get a group size of 1 if you group by a column that is unique

Upvotes: 1

Related Questions