May 8, 2008 at 4:46 am
Hi, I need to count all the stores in a table and provide a list broken down into upper/lower case etc.. Because my DB collation is CI based I had to add the collate clause onto the stor_name to give me all the different cases ie
select distinct stor_name COLLATE SQL_Latin1_General_CP1_CS_AS
from stores
where stor_name like '%simon%'
group by stor_name
When I run this I get the results back as expected
Simon
simon
SIMON
However I also want to count these. If I add count(*) it just returns Simon - 10. There are 10 occurences of this store but I want it broken down into how it really is ie Simon 4, simon 3, SIMON 4
Thanks in anticipation
May 8, 2008 at 5:15 am
simon.letts (5/8/2008)
There are 10 occurences of this store but I want it broken down into how it really is ie Simon 4, simon 3, SIMON 4
4 + 3 + 4 = 11 ?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
May 8, 2008 at 5:17 am
This works for me...
declare @stores table (stor_name varchar(10))
insert @stores
select 'Simon'
union all select 'Simon'
union all select 'Simon'
union all select 'Simon'
union all select 'simon'
union all select 'simon'
union all select 'simon'
union all select 'SIMON'
union all select 'SIMON'
union all select 'SIMON'
union all select 'SIMON'
select stor_name COLLATE SQL_Latin1_General_CP1_CS_AS, count(*)
from @stores
where stor_name like '%simon%'
group by stor_name COLLATE SQL_Latin1_General_CP1_CS_AS
/* Results
---------- -----------
SIMON 4
Simon 4
simon 3
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
May 8, 2008 at 7:34 am
Thanks very much for that...worked fine!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply