Ive been playing with this for a few days and thought I might thow it
out for seggestions.

I have Several Queries that need counts returned

The Queries are Mutually Exclusive meaning whatever Query they return
in first they cannot be included in the counts of any queries below
them.

This set of queries for example


Select ID From Customers where FIRST_NAME = 'Chris' (would return say
150)

Select ID From Customers where ST='OH' (This would retunr say 50, BUT
Run alone it might return 70, however 20 of those were in the first
Query so they arent to be retunred in this result set.

The total for Bot Queries would be 200

But If I reverse it like so
Select ID From Customers where ST='OH' (This now returns 70)
Select ID From Customers where FIRST_NAME = 'Chris' (This now returns
130)

The total of course for BOT Queries is 200 but I dont need that total I
need the total for EACH Query depending on its ordering

What I need are the single counts depending on the order in which the
queries are run

It seems like a recursion problem, but It might go past 32 level so I
cant use recursive SQL ( I dont think )

I've thought of (or tried to think how to use Not In, Not Exist, etc
but still dosent come up with the results....)
How Can I grab the counts for each Query ?

Chris

Given your simple example, you could have two proc's one returns the count
in one order the other likewise, in reverse.
The queries can be modified to exclude the other count ie Select count where
state = 'oh' and name <> 'chris'

for a more robust general purpose query, you could add a working bit column
to the table or maybe use a temp table and 'mark' each row as counted after
doing the specific count. then in all queries only count rows not yet
counted.
update table set countedbit = 0
select count where name = chris
update table set countedbit = 1 where name = chris
(or if using a temp table, perhaps, delete #temp where name = 'chris'
select count where st = 'oh'
etc....


"WertmanTheMad" <cwertman@webchamps.com> wrote in message
news:1103558981.388844.318540@z14g2000cwz.googlegr oups.com...
> Ive been playing with this for a few days and thought I might thow it
> out for seggestions.
>
> I have Several Queries that need counts returned
>
> The Queries are Mutually Exclusive meaning whatever Query they return
> in first they cannot be included in the counts of any queries below
> them.
>
> This set of queries for example
>
>
> Select ID From Customers where FIRST_NAME = 'Chris' (would return say
> 150)
>
> Select ID From Customers where ST='OH' (This would retunr say 50, BUT
> Run alone it might return 70, however 20 of those were in the first
> Query so they arent to be retunred in this result set.
>
> The total for Bot Queries would be 200
>
> But If I reverse it like so
> Select ID From Customers where ST='OH' (This now returns 70)
> Select ID From Customers where FIRST_NAME = 'Chris' (This now returns
> 130)
>
> The total of course for BOT Queries is 200 but I dont need that total I
> need the total for EACH Query depending on its ordering
>
> What I need are the single counts depending on the order in which the
> queries are run
>
> It seems like a recursion problem, but It might go past 32 level so I
> cant use recursive SQL ( I dont think )
>
> I've thought of (or tried to think how to use Not In, Not Exist, etc
> but still dosent come up with the results....)
> How Can I grab the counts for each Query ?
>
> Chris
>

I can almost be certain there will be between 5 and 100 levels to each
set of queries

I had not thought of a 'marked' row, but unfortunatley it wont work due
to conccurent users accesing the same table.

I am still unsure of temp tables, Im just a little leary, an in memory
temp table may be al right as I can make sure Im pretty clean

Thanks for the suggestion.

Any others out there ?

Chris


kevin ruggles wrote:
> Given your simple example, you could have two proc's one returns the
count
> in one order the other likewise, in reverse.
> The queries can be modified to exclude the other count ie Select
count where
> state = 'oh' and name <> 'chris'
>
> for a more robust general purpose query, you could add a working bit
column
> to the table or maybe use a temp table and 'mark' each row as counted
after
> doing the specific count. then in all queries only count rows not yet

> counted.
> update table set countedbit = 0
> select count where name = chris
> update table set countedbit = 1 where name = chris
> (or if using a temp table, perhaps, delete #temp where name = 'chris'
> select count where st = 'oh'
> etc....
>
>
> "WertmanTheMad" <cwertman@webchamps.com> wrote in message
> news:1103558981.388844.318540@z14g2000cwz.googlegr oups.com...
> > Ive been playing with this for a few days and thought I might thow
it
> > out for seggestions.
> >
> > I have Several Queries that need counts returned
> >
> > The Queries are Mutually Exclusive meaning whatever Query they
return
> > in first they cannot be included in the counts of any queries below
> > them.
> >
> > This set of queries for example
> >
> >
> > Select ID From Customers where FIRST_NAME = 'Chris' (would return
say
> > 150)
> >
> > Select ID From Customers where ST='OH' (This would retunr say 50,
BUT
> > Run alone it might return 70, however 20 of those were in the first
> > Query so they arent to be retunred in this result set.
> >
> > The total for Bot Queries would be 200
> >
> > But If I reverse it like so
> > Select ID From Customers where ST='OH' (This now returns 70)
> > Select ID From Customers where FIRST_NAME = 'Chris' (This now
returns
> > 130)
> >
> > The total of course for BOT Queries is 200 but I dont need that
total I
> > need the total for EACH Query depending on its ordering
> >
> > What I need are the single counts depending on the order in which
the
> > queries are run
> >
> > It seems like a recursion problem, but It might go past 32 level so
I
> > cant use recursive SQL ( I dont think )
> >
> > I've thought of (or tried to think how to use Not In, Not Exist,
etc
> > but still dosent come up with the results....)
> > How Can I grab the counts for each Query ?
> >
> > Chris
> >

I think I got it, I did in VB.Net (I am more comfy there for quick and
dirty) The converted it into t-sql

Its just looping with a cursor building SQL out of my table (The table
already has asql query in it, then its just a matter of stringing it
together.

WHILE @@FETCH_STATUS = 0
BEGIN

set @SQL = ' AND AID NOT IN (' + @TEST_QW_SQL + ')'

if @TEST_QW_ORDER >= @Query_Order_Number

set @UPD_SQL = 'update woi_d.dbo.testqw set test_qw_count = (Select
Count(DISTINCT ID) from QW2_TABLE WHERE AID IN(' + @TEST_QW_SQL + ')' +
@notinstring +
') where test_qw_id = ''' + convert(varchar(10), @test_qw_id) + ''''

exec (@UPD_SQL)

FETCH NEXT FROM cur_WhereClause
INTO @TEST_QW_ID, @TEST_QW_SQL, @TEST_QW_ORDER
set @notinstring = @SQL + @notinstring

END



Chris

WertmanTheMad (cwertman@webchamps.com) writes:
> Ive been playing with this for a few days and thought I might thow it
> out for seggestions.
>
> I have Several Queries that need counts returned
>
> The Queries are Mutually Exclusive meaning whatever Query they return
> in first they cannot be included in the counts of any queries below
> them.
>
> This set of queries for example
>
>
> Select ID From Customers where FIRST_NAME = 'Chris' (would return say
> 150)
>
> Select ID From Customers where ST='OH' (This would retunr say 50, BUT
> Run alone it might return 70, however 20 of those were in the first
> Query so they arent to be retunred in this result set.
>
> The total for Bot Queries would be 200
>
> But If I reverse it like so
> Select ID From Customers where ST='OH' (This now returns 70)
> Select ID From Customers where FIRST_NAME = 'Chris' (This now returns
> 130)
>
> The total of course for BOT Queries is 200 but I dont need that total I
> need the total for EACH Query depending on its ordering

This certainly does not sound like a standard problem. Clearly you do
need to use some sort of a temp table. Here is one idea, which is build
on the assumption that all queries are on the form

SELECT ID FROM customers WHERE ....

(if the queries are not, the complexity of the problems increases.)

I also assume that you have full control over how the code is generated.
Then you could do:

CREATE TABLE #temp (queryno int NOT NULL,
ID int NOT NULL)

SELECT 1, ID FROM customers WHERE ST = 'GH'
UNION ALL
SELECT 2, ID FROM customers WHERE first_name = 'Chris'
...

DELETE #temp
FROM #temp a
WHERE EXISTS (SELECT *
FROM #temp b
WHERE a.ID = b.ID
AND b.queryno < a.queryno)

-- This would be your answer.
SELECT queryno, COUNT(*)
FROM #temp
GROUP BY queryno
ORDER BY queryno

DROP TABLE #temp


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

1) You might want to look at Cognos or other tools that are meant for
this kind of thing. It is a lot cheaper in the long run.

2) What if you build a VIEW like this?

CREATE VIEW Tallies (cust_id, c1,c2,.. cn)
AS
SELECT cust_id,
CASE WHEN first_name = 'Chris'
THEN 1 ELSE 0 END AS c1,
CASE WHEN state_code = 'OH'
THEN 1 ELSE 0 END AS c2,
...
CASE WHEN <cond-n>
THEN 1 ELSE 0 END AS cn
FROM Customers
GROUP BY cust_id;

Now you can write queries of the form:

SELECT COUNT(*)
FROM Summary
WHERE ck = 1
AND 1 NOT IN ( c1,.. c[k-1]);

I preserved the raw data at the customer level, however you could have
written your view as a derived table then used case expressions again
to get a monster summary table using the above pattern:

CREATE VIEW Summary (c1,c2,.. cn)
AS
SELECT
CASE WHEN c1 = 1
THEN 1 ELSE 0 END AS c1,
CASE WHEN c2 = 1
AND (c1 = 0)
THEN 1 ELSE 0 END AS c2,
...
CASE WHEN <cond-n> = 1
AND 1 NOT IN (c1, c2,.. c[n-1])
THEN 1 ELSE 0 END AS cn
FROM (.. ) AS Tallies (cust_id, c1,c2,.. cn)
GROUP BY cust_id;

This should run in one tablescan and a sort. No temp tables, no
recursion, no proprietary code.

You can also generate other queries from Tallies based some simple
predicates, math and the SIGN() function.