By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,423 Members | 1,601 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,423 IT Pros & Developers. It's quick & easy.

Mutually exclusive counts on ordered queries

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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" <cw******@webchamps.com> wrote in message
news:11**********************@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

Jul 23 '05 #2

P: n/a
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" <cw******@webchamps.com> wrote in message
news:11**********************@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


Jul 23 '05 #3

P: n/a
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

Jul 23 '05 #4

P: n/a
WertmanTheMad (cw******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5

P: n/a
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.

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.