Should I include the same function which I have used in the select in the group by?

Should I include the same function which I have used in the select in the group by?

Select Coalesce(CountryID,1), Count(1)
From Tbl1
Group BY CountryID

Or should I script it as

Select Coalesce(CountryID,1), Count(1)
From Tbl1
Group BY (CountryID,1)

Mr_ShawAsked:
Who is Participating?
 
ThomasianConnect With a Mentor Commented:
Actually, they could return different results so it depends on what you need.

Here's an example
DECLARE @tbl1 table (CountryID int)
INSERT @tbl1
SELECT 1
UNION ALL SELECT NULL
UNION ALL SELECT NULL

SELECT coalesce(CountryId,1) id, COUNT(1) [count]
from @tbl1
group by countryid
/*
id	count
1	2
1	1
*/

SELECT coalesce(CountryId,1) id, COUNT(1) [count]
from @tbl1
group by coalesce(CountryId,1)
/*
id	count
1	3
*/

Open in new window

Note that in the first query, there are 2 records with id=1, while it was combined on the second query.
0
 
MarioAlcaideConnect With a Mentor Commented:
The second option looks better to me
0
 
Barry CunneyConnect With a Mentor Commented:
Yes second option
Select Coalesce(CountryID,1), Count(1)
From Tbl1
Group BY COALESCE(CountryID,1)

By using this any records with NULL value for CountryID will be given a 1 and the data will be grouped on this 1 as opposed to trying to group by NULL

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LowfatspreadConnect With a Mentor Commented:
yes your group by should contain the coalesce...

but it does depend as thomasian demonstrates what your really trying to achieve...


Select coalesce(country,1) as country,thecount
from (
select country,count(*) as thecount
from tbl1
group by country
) as x
order by 1
 
0
 
Aaron ShiloConnect With a Mentor Chief Database ArchitectCommented:
yes you should

the coalesce takes care of you null values.
AGG function diregard Null values.

if you dont use the Function then the results will be worng.
0
 
Mr_ShawAuthor Commented:
thanks
0
All Courses

From novice to tech pro — start learning today.