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

A Better Way (Complicated Select Statement)

After hours of trying, I finally got a select statement to return what I needed, but I am not sure how I am doing is the most efficient way. Please give me your input.

Here's the situation: I want to allow customers to add to their magazine subscriptions online, so I created an aspx form that shows the magazines they are currently subscribed to and allows them to choose from other "available" magazines. In the available magazines field, I want to show all magazines that we have minus the ones the customer is already subscribed to.

MY pb table lists all of the magazines. The info table lists customers and their current magazine subscriptions.

I also have a table, PBExclusive that is for magazines that we have, but only want to be available to certain customers. So, I also need to make sure the "available" magazines field doesn't list any of the exclusions unless the customer is listed as the exception for that magazine.

Here is what I have:

("SELECT p.code, p.WebName FROM pb p WHERE (p.code IN(Select e.code FROM PBExclusive e WHERE e.id = " & lblID.Text & ") OR p.code NOT IN(Select e.code From PBExclusive e Where e.code = p.code)) AND p.code NOT IN (SELECT i.code FROM info i, pb p WHERE i.id = " & lblID.Text & " AND i.code = p.code)AND p.contract <> '1' AND p.code <> '00' AND WebListing <> '0' AND p.code <> '' AND p.code <> 'SU' AND p.code<> 'AGC' ORDER BY p.WebName", conn)

Hi J,

Do you have a table that includes customers only?

Does your PBExclusive join the customers to the PB table with all the magazines?

To get the list of the magazines a customer doesn't have already, Left Join the PB list of magazines to the Info table using "...Where Info.Whatever Is Null..." It's harder to eliminate the exclusive magazines without more information about what this table looks like and how it relates to the others.