Not sure how to GROUP BY in this case

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 2000 Forums

SQL Server Development (2000)

Not sure how to GROUP BY in this case

Author

Topic

jbezanson
Starting Member

35 Posts

Posted - 2006-06-26 : 10:13:00

I have the following query that gives me part of what I want but fully. I am using the Northwind database in SQL Server 2000

SELECT Country, COUNT(Country) AS '# of Reps', 
	(
	SELECT COUNT(*) 
	FROM Customers 
	WHERE ContactTitle='Owner'
	) AS 'Owners'
FROM Customers
GROUP BY Country

It selects the country and # of Reps correctly but the 'Owners' column always says 17 (it is counting every record not just for each country). I am not sure how to do this.

Thanks for your help

Justin Bezanson

jbezanson@spatterdesign.ca
www.aspnetguy.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-26 : 10:16:30

[code]SELECT Country,
COUNT(*) [# of Reps],
SUM(CASE WHEN ContactTitle = 'Owner' THEN 1 ELSE 0 END) Owners
FROM Customers
GROUP BY Country
ORDER BY Country[/code]
Peter Larsson
Helsingborg, Sweden

jbezanson
Starting Member

35 Posts

Posted - 2006-06-26 : 10:18:48

Thanks so much Peso

Justin Bezanson

jbezanson@spatterdesign.ca
www.aspnetguy.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-26 : 10:23:32


SELECT		Country,
		COUNT(Country) '# of Reps',
		COUNT(CASE WHEN ContactTitle = 'Owner' THEN 1 END) 'Owners'
FROM		Customers
GROUP BY	Country

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-26 : 10:29:46

quote:

Originally posted by madhivanan


SELECT		Country,
		COUNT(Country) '# of Reps',
		COUNT(CASE WHEN ContactTitle = 'Owner' THEN 1 END) 'Owners'
FROM		Customers
GROUP BY	Country

I wouldn't since the message Warning: Null value is eliminated by an aggregate or other SET operation. is sent back to front-end application and could cause unpredicted results, depending on error handling.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-26 : 10:32:52

Though you use SUM(CASE...), you get same warning. Right?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-26 : 10:35:45

quote:
Originally posted by madhivanan

Though you use SUM(CASE...), you get same warning. Right?

No. Only (21 row(s) affected) is returned but this can be avoided and should be, with SET NOCOUNT ON.
It's a good practice from ASP and Visual Basic to always use SET NOCOUNT ON, because in certain circumstances the message (21 row(s) affected) is sent to the client.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-26 : 10:45:44

Well. If Country is not null, you wont get the warning otherwise you will


Declare @Customers table(country varchar(100), ContactTitle varchar(100))
Insert into @Customers
	Select 'India', 'Manager' union all
	Select 'India', 'Owner' union all
	Select 'England', 'Manager' union all
	Select 'India', NULL union all
	Select NULL, 'Owner' 


SELECT		Country,
		COUNT(Country) '# of Reps',
		SUM(CASE WHEN ContactTitle = 'Owner' THEN 1 ELSE 0 END) 'Owners'
FROM		@Customers
GROUP BY	Country



SELECT		Country,
		COUNT(Country) '# of Reps',
		COUNT(CASE WHEN ContactTitle = 'Owner' THEN 1 END) 'Owners'
FROM		@Customers
GROUP BY	Country

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-26 : 11:46:51

quote:
Originally posted by madhivanan

Well. If Country is not null, you wont get the warning otherwise you will

True. But changing

COUNT(Country) '# of Reps'

COUNT(*) '# of Reps'

will take care of that. See the different outputs too!

Using COUNT(*) displays

Country  # of Reps  Owners
-------	 ---------  ------
NULL             1       1
England          1       0
India            3       1

which is correct, and using COUNT(Country) shows

Country  # of Reps  Owners
-------	 ---------  ------
NULL             0       1
England          1       0
India            3       1

which doesn't count # of Reps well and produces the warning as well.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-27 : 01:49:39

Yes. What I wanted to tell you is the warning may come from various methods and not just by using COUNT(CASE...)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 02:21:25

Of course. That's why the discussions here are meaningful and learning.

COUNT(NULL) will always produce an error since NULL is a not known value. But SUM(1) or SUM(0) will never.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources