473,322 Members | 1,241 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Mutually exclusive counts on ordered queries

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
5 3699
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: wooks | last post by:
I have defined a schema with an xsd:choice element for 2 mutually exclusive fields. When both are present I get an error which is good, but what is not so good is the error message which says...
3
by: softengine | last post by:
Can and how do you alter a data view to include a look up field from another data table? The data table of the dataview only has the key, the value I need is in another data table. Can and how...
2
by: js | last post by:
I include a asp:RadioButton in an ItemTemplate of a DataGrid like the following. However, the radio buttons are not mutual exclusive where select one will deselect the rest of the radio buttons. I...
2
by: Stimp | last post by:
I'm getting the error: 'SelectedIndex' and 'SelectedValue' attributes are mutually exclusive when I try to populate a series of dropdowns... any idea what I could be doing wrong here? ...
5
by: Stimp | last post by:
Hi all, I've come back to this problem again and I've identified which part of my code is producing the error: "'SelectedIndex' and 'SelectedValue' attributes are mutually exclusive" I...
0
by: sloan | last post by:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;316495 Radio Buttons Are Not Mutually Exclusive When Used in a Repeater Server Control ... Has 2.0 fixed this bug ?
0
by: sloan | last post by:
BUG: Radio Buttons Are Not Mutually Exclusive When Used in a Repeater Server Control http://support.microsoft.com/default.aspx?scid=kb;EN-US;316495 I programmed up a pretty simple page in 2.0. ...
8
by: arun | last post by:
Hi Can any one suggest me how to access the mutually exclusive check boxes in a table of 7 rows by 14 columns. Only one can be selected in each row. Other than checking each one with if...
2
by: arun | last post by:
Hi Can any one suggest me how to access the mutually exclusive check box extender in a table of 7 rows by 14 columns. Only one can be selected in each row. Other than checking each one with if...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.