Explination of Group by question

[djj55]

Hello, SQL 2000
I have the following (note this had been moved from Access)

SELECT P.TracNum 
    , MAX(P.[PDate]) AS [MaxOfPDate] 
    , MIN(P.[PRecd]) AS [PRecd] 
FROM Table1 A 
INNER JOIN Table2 P
ON A.TracNum = P.TracNum
GROUP BY P.TracNum, A.[CName], P.[PRecd]
HAVING A.[CName] Like 'Aname%' AND P.[PRecd] Is Not Null

My question is why the difference in returned records when the GROUP BY clause is changed? If I use just TracNum I get a different result than when I use all three.

I can sort of see why PRecd would be different but the CName throws me. And yes Aname% has several variations.

Thank you,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[RiverGuy]

If you just just TracNum to GROUP BY, you are aggregating by TracNum. So if you have three different TracNum values, you have three rows returned. If you GROUP BY the other two, you will be aggregating by each distinct combination of TracNum, CName and PRecd. Usually, if you are not displaying a column, you don't GROUP BY it.

 

[djj55]

Thank you, I just found that out after more checking. It seems they really bungled the query in Access before they continued the problem with the T-SQL version.

I have changed HAVING to WHERE and am only using the TracNum as should be for this application.

Thank you again,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Try thinking of it this way...

Each row in the output represents a group of data. How is that group defined? By the GROUP BY clause. When you change the group by clause, it is common to get different results. You CAN get the same results, but that would only happen if there was only one group of data to begin with.

In your example, when you ONLY have TracNum in the group by, you are guaranteed to get only 1 row for each unique TracNum value. Since you are joining to another table, it's possible that TracNum's might be missing. But... for each TracNum that exists in BOTH tables, you will get exactly one row.

When you add additional columns to the group by, it is possible (and likely) that there will be duplicates of each column value, but you will get a distinct list of the column combinations.

Think of it this way. Suppose you have a table with State, Gender, and Salary. If you run a query like this...

Select State, Avg(Salary) From Table Group By State

You will get a list of states and an average salary for all the residents in each state.

Now, if you run this...

Select State, Avg(Salary) From Table Group By State, Gender

You will likely get 2 rows for each state because you are now grouping by 2 columns. There will be a row for each state and another row for each gender. Of course, you're not returning the gender column so you wouldn't be able to determine which row was for men, and which one was for women. Now, imagine a situation where there were no men living in a particular state. When you group by state, you will get one row. When you group by State and Gender, you would still only get one row (because no men live in this fictitious state).

Does this make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thank you both.

George as you see I posted just before you (while you were typing I suppose). Always like your explanations.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

I blame you, djj! After all, your title was...

"[!]Explination[/!] of Group by question"



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom