July 7, 2011 at 1:17 pm
Hi all,
Suppose, I have employee table in which i have Employee Name stored in one column.
I want to calculate count of employees as per first character(Alphabetical Count).
In that i want to show the output as
Alphabet Count
A
B
C
D
E
F
.
.
.
.
X
Y
Z
& IN COUNT THEIR RESPECTIVE COUNT.
I wrote a query as follows
select SUBSTRING(Name,1,1),COUNT(*)
from Emp
group by SUBSTRING(Name,1,1)
This query gives me output, But not the required output.
It gives me output for all character.But don't give me count of some characters which are not there in table.
Means suppose if there is no employee whose name starts with 'X' then in output it doesn't give me count of char 'X'.
I want if there is no employee with any character like 'X' then it should show me 'X' and the count for that character as ZERO.
How can i achieve it?
Thanks in advance.
Roshan
July 7, 2011 at 1:25 pm
GROUP BY takes all the results in the table and aggregates them. It has no way of knowing about any data which doesn't exist.
If you want to achieve your results, the best bet is to create a new table that just stores letters. You can make this a temp table in your procedure.
Then once you have this letter table, make your query like:
select Letter, COUNT(Name)
from #Letters
LEFT JOIN Emp ON SUBSTRING(Name,1,1) = Letter
group by Letter
July 7, 2011 at 1:29 pm
kramaswamy (7/7/2011)
select Letter, COUNT(*)from #Letters
LEFT JOIN Emp ON SUBSTRING(Name,1,1) = Letter
group by Letter
SUBSTRING is not SARGable, so it's better to write this using LIKE.
select Letter, COUNT(*)
from #Letters
LEFT JOIN Emp ON Name LIKE Letter + '%'
group by Letter
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 7, 2011 at 1:29 pm
*Slight correction to post, sorry, forgot that you need to COUNT(Name) instead, that way you'll get the 0 results for the ones that don't have any records.
July 7, 2011 at 1:30 pm
Since this is a rather strange requirement, may I ask if this is ome kind of homework?
Anyhow, you'll need a table (or subquery or CTE) with all character values you'd like to report against (I don't know if you want to differentiate upper and lower case or not). Use this data in a left join.
July 7, 2011 at 1:41 pm
Thanks for reply...
It worked out.
But can't we have any other way without using a temp table.
If possible..
Actually, I wrote one more query it worked out as per my requirement but its too long.
select 'A' as Alphabet,count(Name) from emp where Name like 'a%'
union
select 'B' as Alphabet,count(Name) from emp where Name like 'b%'
union
.
.
.
.
same till character Z
So it became 26 qurie union. But i don't think it would be efficient.
So i am searching for any other technique without using a temp table.
July 7, 2011 at 1:45 pm
Like LutzM said, you could use a CTE instead if you'd like, or make a physical table with the data. But bottom line is you need some way of telling the procedure, that there are other letters that exist beyond the ones that are contained in your table.
July 7, 2011 at 1:49 pm
Create a new table and store the alphabets, and by the following query u must get ur output....
select Letter, COUNT(Name)
from #Letters
LEFT JOIN Emp ON SUBSTRING(Name,1,1) = Letter
group by Letter
Regards,
Arunkumar
MCTS - SQL Server 2005
July 7, 2011 at 1:50 pm
Again though be sure you don't use COUNT(*), since that will return a value of 1 even if the LEFT JOIN produces no results. Use COUNT(Name).
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply