Counting

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

It looks simple but I couldn't solve the problem so far:

I have a single-column table. The values are boolean.
The column looks like this : 0 0 0 -1 -1 0 -1 -1 -1 0 -1 -1 0 0 etc.
I need to write SELECT statement counting all 0s and -1s like this :

for 0s : 4 1 1 2

for -1s: 2 3 2

(from the column data above)

Can anybody help me with this?

Thanks,

Greg
 
It looks simple but I couldn't solve the problem so far:

I have a single-column table. The values are boolean.
The column looks like this : 0 0 0 -1 -1 0 -1 -1 -1 0 -1 -1 0 0 etc.
I need to write SELECT statement counting all 0s and -1s like this :

for 0s : 4 1 1 2

for -1s: 2 3 2
Click to expand...

If you don't have some other field indicating the order of the
records, I suspect that this is simply impossible to do in SQL (I'd be
intrigued to be proved wrong). Tables have no order, so there is
nothing in your table distinguishing the first zero from the last one,
much less letting you count consecutive zeroes.


John W. Vinson[MVP]
 
...I suspect that this is simply impossible to do in SQL (I'd be
intrigued to be proved wrong).
Click to expand...

i'd be intrigued to find out what real-life scenario would necessitate doing
it. <g>
 
That's exactly what I want to do- to count -1s and 0s for the given
order. I can add to the table a primary key column: 1 2 3 4 5 etc, but
it will hardly help. It looks like there is no way to group 0s and -1s
using SQL, which is wrong in my opinion.
This table (and several more dosens) is an intermediate result of
processing some time series data. It is real life problem.

Thanks John and Tina,

Greg
 
That's exactly what I want to do- to count -1s and 0s for the given
order. I can add to the table a primary key column: 1 2 3 4 5 etc, but
it will hardly help. It looks like there is no way to group 0s and -1s
using SQL, which is wrong in my opinion.
This table (and several more dosens) is an intermediate result of
processing some time series data. It is real life problem.
Click to expand...

A monstrous SQL query might be possible, but I'm guessing that VBA
code would be much better. You could open a Recordset based on the
table and step through it with MoveNext, accumulating counts of 0's
and -1's. It would actually be pretty simple code.


John W. Vinson[MVP]
 
well, you certainly *can* group 0's and -1's in SQL. but if i understood you
correctly, you want to group and count the records based *first* on their
inherent order in the table, and then on their 0 or -1 value. and as John
said, records in an Access table have no *inherent* order. they only have
whatever order is imposed on them by indexing a field, or by imposing a
SortOrder at runtime.

it sounds like John has a solution in mind using VBA, though, and you
couldn't ask for better help than his, so i'll leave you in his capable
hands. :)

hth
 
John said:
A monstrous SQL query might be possible, but I'm guessing that VBA
code would be much better. You could open a Recordset based on the
table and step through it with MoveNext, accumulating counts of 0's
and -1's.
Click to expand...

John, Glad to hear you have got the message that saying a Jet table is
an 'unordered bucket of data' is not true <g>; if it were, your
solution would be unworkable.

The fact that a table with no PRIMARY KEY designation is physically
stored in (relative) date/time entered order and that in absence of an
explicit ORDER BY clause the data will retain this order can be further
exploited, along with some other 'features' of Jet...

Consider this as being the OP's base table:

CREATE TABLE Test (
data_col INTEGER DEFAULT 0 NOT NULL,
CHECK (data_col IN (0, -1))
)
;
INSERT INTO Test VALUES (0)
;
INSERT INTO Test VALUES (0)
;
INSERT INTO Test VALUES (0)
;
INSERT INTO Test VALUES (-1)
;
INSERT INTO Test VALUES (-1)
;
INSERT INTO Test VALUES (0)
;
INSERT INTO Test VALUES (-1)
;
INSERT INTO Test VALUES (-1)
;
INSERT INTO Test VALUES (-1)
;
INSERT INTO Test VALUES (0)
;
INSERT INTO Test VALUES (-1)
;
INSERT INTO Test VALUES (-1)
;
INSERT INTO Test VALUES (0)
;
INSERT INTO Test VALUES (0)
;

Now create a working table which will apply a row ID to the original
data in date/time entered order by exploiting the non-relational
autonumber (a.k.a. IDENTITY) feature:

CREATE TABLE WorkingTable (
row_ID INTEGER IDENTITY(1, 1) NOT NULL,
data_col INTEGER DEFAULT 0 NOT NULL,
CHECK (data_col IN (0, -1))
)
;
INSERT INTO WorkingTable (data_col)
SELECT data_col FROM Test
;

With the row_ID, I trust you can see how this task is a whole lot
easier. Here's one way of detecting on which row the data sequence
changes:

SELECT W1.row_ID, W1.data_col,
(SELECT MAX(W2.row_ID)
FROM WorkingTable AS W2
WHERE W1.row_ID > W2.row_ID AND W1.data_col <> W2.data_col) AS change
FROM WorkingTable AS W1
;

Put this in a Query object (a.k.a. VIEW) to make subsequent querying
easier:

CREATE VIEW WorkingView
AS
SELECT DISTINCT IIF(DT1.change IS NULL, 0, DT1.change) AS row_ID,
DT1.data_col
FROM (
SELECT W1.row_ID, W1.data_col,
(SELECT MAX(W2.row_ID)
FROM WorkingTable AS W2
WHERE W1.row_ID > W2.row_ID AND W1.data_col <> W2.data_col) AS change
FROM WorkingTable AS W1
) AS DT1
;

Then we can exploit another 'feature' of Jet i.e. 'calculating
correlation names':

SELECT v1.data_col,
v1.row_id AS start_row, (
SELECT MIN(v2.row_id)
FROM WorkingView AS V2
WHERE v2.row_id > v1.row_id
) AS end_row,
end_row - start_row AS count_of
FROM WorkingView AS V1
ORDER BY v1.data_col, v1.row_id
;

A special case is required to handle the final value but you get the
idea. I'm sure there's a short cut I'm not seeing (is my SQL
monstrous?) but my coffee is already cold ;-)
 
I've already written the code in c# to handle the problem. I just
thought it's possible to do it i SQL writing some simple query. I
wanted for some reasons to keep it all in the database
Thanks to all.
 
Back
Top