Addition During a Count - 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)

Addition During a Count

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-12 : 15:07:48

Hi

Is there anyway to perform a addition on two items in a COUNT query?
For example if i use the followig query:

SELECT
Region
,COUNT(*)
FROM Regions
GROUP BY Regions

It would give me the following results:

Region1 22
Region2 33
Region3 21
Region4 4
Region5 9
Region6 61

However I would want it display the the combined totals Region1 and Region2 together so it would give the figure of 55.

Is this possible?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 15:15:29

A way to do this is

SELECT t.Region,COUNT(*)
FROM
(SELECT
Case WHEN Region='Region1' OR Region='Region2' THEN 'Region12'
     ELSE Region
     END AS Region
,other fields
FROM Regions)t
GROUP BY t.Region

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-12 : 15:45:46

Hi

Thanks for the quick reply.

Tried your query but it still giving the same result.

Any other ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-12 : 16:06:09

His solution works fine for me:

declare @regions table (region varchar(10), someothercolumn int)

insert into @regions values('Region1', 1)
insert into @regions values('Region1', 2)
insert into @regions values('Region2', 3)
insert into @regions values('Region2', 4)
insert into @regions values('Region2', 5)
insert into @regions values('Region3', 6)
insert into @regions values('Region4', 7)
insert into @regions values('Region5', 8)

SELECT t.Region,COUNT(*)
FROM
(SELECT
Case WHEN Region='Region1' OR Region='Region2' THEN 'Region12'
ELSE Region
END AS Region
FROM @Regions)t
GROUP BY t.Region

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-12 : 17:19:32

Hi

Thanks for that!

Is there a away to show only the combined results. i.e just the figure for Region12.

Reason being is that I am planning to use the value in SQL reporting services.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-12 : 17:23:52

Then just filter it:

SELECT 'Region12', COUNT(*)
FROM Regions
WHERE Region IN ('Region1', 'Region2')

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-12 : 17:36:08

Hi

Thanks thats exactly what I needed.

You guys have been a GREAT help!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources