Group by all - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Group by all

Author

Topic

nenne
Starting Member

7 Posts

Posted - 2009-04-20 : 05:07:11

Hi, Can someone help me why i got this result from a query

Besok is a value 1 or 2
F1 is between 1 and 4

SELECT Besok, F1, COUNT(F1) AS antal
FROM mytable
GROUP BY ALL F1, Besok
ORDER BY F1

And the result is:

Besok F1 antal
2 NULL 0
1 1 0
2 1 0
1 2 0
2 2 0
1 3 0
2 3 0
1 4 0
2 4 0

Why do i get the first row with null. This happens only on my production database, not in my developing envionment.

Database is SQL2005

Any clue?

Lennart

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 05:08:40

You are BOTH grouping by column F1 and counting the occurencies of column F1.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 05:13:17

The reason for NULL, is that some record(s) in your production database has NULL for F1 column.

E 12°55'05.63"
N 56°04'39.26"

nenne
Starting Member

7 Posts

Posted - 2009-04-20 : 05:20:31

Database is empty, as you can see of the result

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 05:25:33

Table is not empty. If the table were empty you would be an empty resultset back.
See this example.

DECLARE	@Sample TABLE
	(
		Besok INT,
		F1 INT
	)

-- On empty table
SELECT		Besok,
		F1,
		COUNT(F1) AS antal
FROM		@Sample
GROUP BY ALL	F1,
		Besok
ORDER BY	F1

-- Populate table
INSERT	@Sample
SELECT	1, 1 UNION ALL
SELECT	1, NULL UNION ALL
SELECT	2, 0

-- On populated table
SELECT		Besok,
		F1,
		COUNT(F1) AS antal
FROM		@Sample
GROUP BY ALL	F1,
		Besok
ORDER BY	F1

E 12°55'05.63"
N 56°04'39.26"

nenne
Starting Member

7 Posts

Posted - 2009-04-20 : 08:13:45

I made a backup of database and restored it on another server. The first row with null value did not show up when using the same query. I belive there must be som konfiguration issue?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 08:19:45

Are you sure you are showing us the full query?
If you have used WITH CUBE, or WITH ROLLUP on conjunction with GROUP BY, you will get a NULL value as result.

E 12°55'05.63"
N 56°04'39.26"

nenne
Starting Member

7 Posts

Posted - 2009-04-20 : 08:25:41

I prommise, it's the same query

nenne
Starting Member

7 Posts

Posted - 2009-04-20 : 08:41:54

You got the full query

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-20 : 09:10:50

Post some sample data with expected output

Madhivanan

Failing to plan is Planning to fail

nenne
Starting Member

7 Posts

Posted - 2009-04-20 : 09:35:33

I am expecting all rows except the first, with the null value

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-20 : 09:46:25

It's definitely a row with a NULL value in the F1 column.

Do this to see if it returns any rows
SELECT F1 FROM mytable WHERE F1 IS NULL

If it does return a row, then you know you have a data integrity issue.

nenne
Starting Member

7 Posts

Posted - 2009-04-20 : 10:23:01

Sorry, but of cause there is a where in the query to filter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources