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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.