CODEWITHSUNDEEP
sql-servergroup-by
Juliet
Juliet

Reputation: 81516

Group by clause work in one environment, but not another

I have an absolutely baffling case in front of me. I have two database environments, A and B, where environment B has been created from a backup of A. I'm running a rather simple query:

SELECT
    customers.customerName           (* varchar(100), not null *)
    ,countries.countryName           (* varchar(100), not null *)
    ,Balance = Sum(invoices.Amount)  (*        money, not null *)
FROM invoices
INNER JOIN customers
    ON customers.customerID = invoices.customerID
INNER JOIN countries
    ON countries.countryID = customers.countryID
GROUP BY
    customers.customerName
    ,countries.countryName
HAVING
    Sum(invoices.Amount) <> 0
ORDER BY
    customers.customerName

The query returns a list of users who have a non-zero balance on their invoice. The results from environment A resemble the following:

customerName        countryName        Balance
------------        -----------        -------
A                   United States       20.0000
B                   United States       -5.0000
C                   Canada             199.9900
D                   Canada              -0.0100
E                   United States       55.5900

The results on environment B are as follows:

customerName        countryName        Balance
------------        -----------        -------
A                   United States       10.0000
A                   United States       -5.0000
A                   United States       -1.0000
A                   United States       17.0000
A                   United States       -1.0000
B                   United States       -1.0000
B                   United States       -4.0000
C                   Canada             100.9900
C                   Canada              99.9900
...

Query works fine on Environment A, but it looks as if the GROUP BY clause is being ignored altogether in Environment B.

The query works as expected when I comment out Sum(invoices.Amount) from the SELECT and HAVING clause, so its definitely connected to my invoices.Amount field.

I've backed up A and restored it to B, and SqlDelta (a diff tool for databases) shows that databases are identical, so its not a data issue, it has to be a configuration problem -- but I have no idea where to look.

In case its relevant, both environments are using SQL Server 2000 on a Windows Server 2003. Both environments are installed on two separate servers, separate instances of SQL Server, and separate instances of Windows Server 2003.

What could cause SQL server not to group the invoices.Amount field?

Upvotes: 2

Views: 617

Answers (3)

Juliet
Juliet

Reputation: 81516

It turns out there was no error with the query, it was an environment problem. Environment A had SQL Server 8.0.2039, where environment B has SQL Server 8.0.1053. I've updated environment B with the latest service packs and now my query works as expected.

Thanks for your suggestions everyone :), but now I have a new problem: who to kill at Microsoft for this hassle.

Upvotes: 2

Matt
Matt

Reputation: 5142

What happens if you comment out the HAVING clause, but leave Sum(invoices.Amount) in the SELECT clause? i.e. can you verify that the sum() function is working?

Also, you might check your database collation (or your default server collation); collation settings can affect some aggregate functions, but admittedly, I don't see why it would alter the results of sum().

Upvotes: 1

Amy B
Amy B

Reputation: 110101

Try this query to attempt to reproduce the problem in a simpler form:

SELECT customerName, Count(*)
FROM customer
GROUP BY customerName
ORDER BY customerName

Try this query to check out the possibility of trailing spaces in the customer name as a reason to group differently.

SELECT customerName, Count(*)
FROM
(
SELECT '|' + customerName + '|' as customerName
FROM customer
) as sub
GROUP BY customerName
ORDER BY customerName

Upvotes: 3

Related Questions