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

Posted on 2011-03-02
Last Modified: 2012-05-11
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)

Question by:Mr_Shaw

Assisted Solution

MarioAlcaide earned 25 total points
ID: 35016110
The second option looks better to me
LVL 22

Accepted Solution

Thomasian earned 190 total points
ID: 35016124
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 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.
LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 95 total points
ID: 35016129
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

LVL 50

Assisted Solution

Lowfatspread earned 95 total points
ID: 35016240
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
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 95 total points
ID: 35027756
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.

Author Closing Comment

ID: 35034853

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question