Sql Conundrum aka confusion big time

[Solarion]

Hey guys. I've been wrestling with this for hours and I've run out of ideas. My sql experience is lets say not that great.

In my first query, I'm able to pull a nice row with totals.



I need to add the LeaveRefNum column though. However the moment I do add it, my totals go crazy:

 

[koeksGHT]

Group by PersalNo?

 

[nfbs]

Query 1. Total = 2 for Persal 24
Query 2: Total = 1 and 1 for Persal 24 because you added leafrefnum and it is two distinct values so you get two distinct row.

 

[prod]

Group by PersalNo?

Already being done. OP maybe you need to return one LeaveRefNum based on a MAX date or such. Your SQL is doing exactly what you're telling it to do as nfbs pretty much explained...

 

[BravoDrie]

Your query will never work the way you are trying too with that data.

You are counting and grouping on a column that have different values.

If you look at your LeaveRefNum... for the same persalno you have two different values in LeaveRefNum. LA/07 and LA/06. as nfbs.

If you leave out LeaveRefNum your counts should work the way you are trying... as q1.

Whenever you are joining to multiple tables and aggregating something and you get these types of results you can bet that there is a column with different values on one of the groupings.

 

[Cam]

Your query will never work the way you are trying too with that data.

You are counting and grouping on a column that have different values.

If you look at your LeaveRefNum... for the same persalno you have two different values in LeaveRefNum. LA/07 and LA/06. as nfbs.

If you leave out LeaveRefNum your counts should work the way you are trying... as q1.

Whenever you are joining to multiple tables and aggregating something and you get these types of results you can bet that there is a column with different values on one of the groupings.

Yeah this is right. Looks like leaverefnum is unique regardless if it is the same user. You won't be able group by user if you include the refnum.