a count query

Hi all,
I need to built an efficient query that would be able to tell me
the amount of times that the field 'mark' has an 'x'. It should count
only one time for a given id. In the following example for id 23 the
count should be only 1 even though it occurs more than once.
At the end, the total count for the example should be 4
instead of 6.
Table A
id mark
== =====
23 x
23 x
25 x
27 x
27
27 x
30
31 x
31
35
Thanks in advance,
CarlosCarlos wrote:
> Hi all,
> I need to built an efficient query that would be able to tell me
> the amount of times that the field 'mark' has an 'x'. It should count
> only one time for a given id. In the following example for id 23 the
> count should be only 1 even though it occurs more than once.
> At the end, the total count for the example should be 4
> instead of 6.
>
> Table A
> id mark
> == =====
> 23 x
> 23 x
> 25 x
> 27 x
> 27
> 27 x
> 30
> 31 x
> 31
> 35
> Thanks in advance,
> Carlos
>
>
SELECT DISTINCT
id,
CASE WHEN mark = 'x' THEN 1 ELSE 0 END AS markcount
FROM table|||Try,
select count(distinct [id]) from tableA where mark = 'x'
AMB
"Carlos" wrote:

>
> Hi all,
> I need to built an efficient query that would be able to tell me
> the amount of times that the field 'mark' has an 'x'. It should count
> only one time for a given id. In the following example for id 23 the
> count should be only 1 even though it occurs more than once.
> At the end, the total count for the example should be 4
> instead of 6.
>
> Table A
> id mark
> == =====
> 23 x
> 23 x
> 25 x
> 27 x
> 27
> 27 x
> 30
> 31 x
> 31
> 35
> Thanks in advance,
> Carlos
>
>|||CREATE TABLE #tbl
(
id INT,
Mark char(1)
);
SET NOCOUNT ON;
INSERT #tbl
SELECT 23,'x'
UNION ALL SELECT 23,'x'
UNION ALL SELECT 25,'x'
UNION ALL SELECT 27,'x'
UNION ALL SELECT 27,''
UNION ALL SELECT 27,'x'
UNION ALL SELECT 30,''
UNION ALL SELECT 31,'x'
UNION ALL SELECT 31,''
UNION ALL SELECT 35,'';
SELECT MarkCount = SUM(c)
FROM
(
SELECT
id,
c = MAX(CASE mark WHEN 'x' THEN 1 ELSE 0 END)
FROM #tbl
GROUP BY id
) x
DROP TABLE #tbl;
"Carlos" <ch_sanin@.yahoo.com> wrote in message
news:%23ODaJQ%23jGHA.1324@.TK2MSFTNGP04.phx.gbl...
>
> Hi all,
> I need to built an efficient query that would be able to tell me
> the amount of times that the field 'mark' has an 'x'. It should count
> only one time for a given id. In the following example for id 23 the
> count should be only 1 even though it occurs more than once.
> At the end, the total count for the example should be 4
> instead of 6.
>
> Table A
> id mark
> == =====
> 23 x
> 23 x
> 25 x
> 27 x
> 27
> 27 x
> 30
> 31 x
> 31
> 35
> Thanks in advance,
> Carlos
>
>|||Tracy McKibben wrote:
> Carlos wrote:
> SELECT DISTINCT
> id,
> CASE WHEN mark = 'x' THEN 1 ELSE 0 END AS markcount
> FROM table
Sorry, copy/pasted the wrong block from QA... What I meant to post was:
SELECT DISTINCT
id,
1 AS markcount
FROM table
WHERE mark = 'x'sql