Count

Hi,

I need to write an SQL query/code to count say E's from a certain table. I am working in SQL Server 2005 (sp2).

For example, consider the table below;

ID From 12 D 12 D 12 C 12 E 12 D 13 D 13 E 13 D 13 E 13 D 14 H 14 D 14 D 14 D 14 D

After the SQL code, I need a result like this table below: In otherwords, I wish to have E's counted if theres E else 0 for that ID.

ID Count E's 12 1 13 2 14 0

Thanks in advance.

try this

SELECT a.[ID]
, COUNT(b.[From]) AS [Count E]
FROM YourTable a LEFT OUTER JOIN
(
SELECT [ID]
. [From]
FROM YourTable
WHERE [From] = 'E'
) b ON a.[ID] = b.[ID]
GROUP BY
a.[ID]|||

This should work reliably:

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( [ID] int,
[From] char(1)
)

INSERT INTO @.MyTable VALUES ( 12, 'D' )
INSERT INTO @.MyTable VALUES ( 12, 'D' )
INSERT INTO @.MyTable VALUES ( 12, 'C' )
INSERT INTO @.MyTable VALUES ( 12, 'E' )
INSERT INTO @.MyTable VALUES ( 12, 'D' )
INSERT INTO @.MyTable VALUES ( 13, 'D' )
INSERT INTO @.MyTable VALUES ( 13, 'E' )
INSERT INTO @.MyTable VALUES ( 13, 'D' )
INSERT INTO @.MyTable VALUES ( 13, 'E' )
INSERT INTO @.MyTable VALUES ( 13, 'D' )
INSERT INTO @.MyTable VALUES ( 14, 'H' )
INSERT INTO @.MyTable VALUES ( 14, 'D' )
INSERT INTO @.MyTable VALUES ( 14, 'D' )
INSERT INTO @.MyTable VALUES ( 14, 'D' )
INSERT INTO @.MyTable VALUES ( 14, 'D' )

SELECT DISTINCT
m.[ID],
[From] = isnull( dt.[From], 0 )
FROM @.MyTable m
LEFT JOIN ( SELECT
[ID],
[From] = count( [From] )
FROM @.MyTable
WHERE [From] = 'E'
GROUP BY [ID]
) dt
ON m.[ID] = dt.[ID]

ID From
-- --
12 1
13 2
14 0

I highly recommend that you avoid using Reserved words for table/column names. FROM is a HIGHLY reserved word, and can only be used by enclosing in double quotes or square brackets.

|||LOL, i haven't tested my post. arnie's post is correct.|||Depending on the distribution of data and what indexes there are on the table, this may be a better solution:

Code Snippet

SELECT
ID,
SUM(

CASE WHEN [From] = 'E'

THEN 1 ELSE 0 END

) AS [Count E's]
FROM @.MyTable
GROUP BY ID

Steve Kass
Drew University
http://www.stevekass.com
|||

You can also use "GROUP BY ALL"

SELECT
[ID],
COUNT(*) AS [Count E's]
FROM @.MyTable
WHERE [From] = 'E'
GROUP BY ALL [ID]

|||

Try using a CASE expression.

select [ID], sum(case when [From] = 'E' then 1 else 0 end) as [Count E's]

from dbo.t1

group [ID]

go

AMB

|||

Thank all.

I tried all the replies and the one that well with my data was Steve's post.

Cheers