Different results when Group By field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Different results when Group By field

Author

Topic

qutip
Starting Member

3 Posts

Posted - 2012-04-04 : 05:52:28

Hello there,

I have a query that I'm building, as I build I test and cross reference the results.

When I run the below query:

SELECT     Dimensions.Depot.Depot, Dimensions.Depot.DepotCode, COUNT(DISTINCT BookingTransaction.BookingRef) AS Bookings, 
                      SUM(BookedCarsList.Duration) AS Duration, Dimensions.Resort.Resort
FROM         Dimensions.Depot INNER JOIN
                      Dimensions.Resort INNER JOIN
                      Dimensions.Location ON Dimensions.Resort.ResortID = Dimensions.Location.ResortID ON 
                      Dimensions.Depot.LocationID = Dimensions.Location.LocationID LEFT OUTER JOIN
                      BookingTransaction WITH (NOLOCK) INNER JOIN
                      BookedCarsList WITH (NOLOCK) ON BookingTransaction.BookingTransactionID = BookedCarsList.BookingTransactionID AND 
                      BookingTransaction.TransactionDate BETWEEN CONVERT(DATETIME, '2011-09-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-09-30 00:00:00', 
                      102) INNER JOIN
                      Bookings WITH (NOLOCK) ON BookingTransaction.BookingRef = Bookings.BookingRef ON 
                      Dimensions.Depot.DepotID = BookedCarsList.PickupDepotID
WHERE     (Dimensions.Resort.Resort = 'Denver')
GROUP BY Dimensions.Depot.Depot, Dimensions.Depot.DepotCode, BookedCarsList.IsCancelled, Dimensions.Resort.Resort, BookingTransaction.BookingRef
HAVING      (SUM(BookedCarsList.Duration) > 0) OR
                      (SUM(BookedCarsList.Duration) IS NULL) OR
                      (BookedCarsList.IsCancelled IS NULL) AND (NOT (BookedCarsList.IsCancelled = 1))

I get the correct number of rows, when I comment out the BookingTransaction.Bookingref as below to get a count of Booking and each Depot I get a different count on certain depots.

SELECT     Dimensions.Depot.Depot, Dimensions.Depot.DepotCode, COUNT(DISTINCT BookingTransaction.BookingRef) AS Bookings, 
                      SUM(BookedCarsList.Duration) AS Duration, Dimensions.Resort.Resort
FROM         Dimensions.Depot INNER JOIN
                      Dimensions.Resort INNER JOIN
                      Dimensions.Location ON Dimensions.Resort.ResortID = Dimensions.Location.ResortID ON 
                      Dimensions.Depot.LocationID = Dimensions.Location.LocationID LEFT OUTER JOIN
                      BookingTransaction WITH (NOLOCK) INNER JOIN
                      BookedCarsList WITH (NOLOCK) ON BookingTransaction.BookingTransactionID = BookedCarsList.BookingTransactionID AND 
                      BookingTransaction.TransactionDate BETWEEN CONVERT(DATETIME, '2011-09-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-09-30 00:00:00', 
                      102) INNER JOIN
                      Bookings WITH (NOLOCK) ON BookingTransaction.BookingRef = Bookings.BookingRef ON 
                      Dimensions.Depot.DepotID = BookedCarsList.PickupDepotID
WHERE     (Dimensions.Resort.Resort = 'Denver')
GROUP BY Dimensions.Depot.Depot, Dimensions.Depot.DepotCode, BookedCarsList.IsCancelled, Dimensions.Resort.Resort /*, BookingTransaction.BookingRef*/
HAVING      (SUM(BookedCarsList.Duration) > 0) OR
                      (SUM(BookedCarsList.Duration) IS NULL) OR
                      (BookedCarsList.IsCancelled IS NULL) AND (NOT (BookedCarsList.IsCancelled = 1))

Any ideas, I'm sure this is a schoolboy error, I just can't see it.

TIA

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-04 : 06:41:51

In relation to the data (we don't know the data) the counts can be different.
In first query you are grouping by BookingRef so a count(DISTINCT BookingRef) should have NO effect.
In the second query there is no grouping on BookingRef so a count(DISTINCT BookingRef) could have effect.

No, you're never too old to Yak'n'Roll if you're too young to die.

qutip
Starting Member

3 Posts

Posted - 2012-04-04 : 06:55:47

Thanks, I realise now without seeing the data it's hard to help.

The reason I put Count(DISTINCT Bookingref) in due to Count(bookingref) was counting duplicate values in the related table, Count(Distinct ... resolved this.

I think the simple resolution to this issue is to create a view and subquery the view, lazy I know but this is taking too much time. Argh.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-04 : 07:03:15

Please think a moment about what GROUP BY is doing.
You then will know why the count on a grouped column can't get anything else but duplicates...

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-04 : 07:17:29

I have to correct this:
In first query you are grouping by BookingRef so a count(DISTINCT BookingRef) should have NO effect.

In first query you are grouping by BookingRef so a count(DISTINCT BookingRef) should have the effect that the counter is always 1.

No, you're never too old to Yak'n'Roll if you're too young to die.

qutip
Starting Member

3 Posts

Posted - 2012-04-04 : 11:37:58

Thanks,

quote:
In first query you are grouping by BookingRef so a count(DISTINCT BookingRef) should have the effect that the counter is always 1.

is kind of what I wanted.

I'm new to this job so I don't fully understand the data yet, which partly why I opted for Count(Distinct as it quickly gave the results I needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources