count troubles

[pondi]

Hi all,

This is my table (simplificated)

cr1 cr2
elt1 2 3
elt2 5 5
elt3 7 4
elt4 9 1
...

For each criteria (cr) I have a constant gate, and i'd like to count the number of elements exceeding the 2 gates, the number over one and not the other and finally the number under these gates.
Thanx

 

[tlbroadbent]

I'm not sure that I understand your question. Assuming you want to compare values in the table to some constant (gate) value and count occurrences over or under the gate value then this query may work. If I've misunderstood, maybe it will provide an idea for you to begin. Let me know if this works.

Select
Sum(Case When cr1 > gate And cr2 > gate Then 1 Else 0 End) As BothOver,
Sum(Case When (cr1 > gate And cr2 < gate) Or (cr1 < gate And cr2 > gate) Then 1 Else 0 End) As OneOver,
Sum(Case When cr1 < gate And cr2 < gate Then 1 Else 0 End) As BothUnder
From Tbl
Terry Broadbent
Please review faq183-874.

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[pondi]

Hi thanx for your help, I think you've catched my goal: in fact my application is graphical, each point or element is placed on the graphical with cr1 against cr2, and I'd like to count the number of points in each part of the graphical.
I've tried your query but the trouble is I use 2 SELECT and it doesn't seem to work with the &quot;case&quot;.
Note: data.typeid are the criteria
7 and 10 are my gates.
This is the beginning of my query:
-------------------------------------------------------
Select
Sum(
Case When (data.typeid=19 AND data.value>7 AND data.ownerid IN (SELECT data.ownerid FROM data WHERE (data.typeid=18 AND data.value>10) AND elements.typeid=4))
Then 1 Else 0 End) As CRITICAL,
--------------------------------------------------------
So it doesn't work.
Yesterday I tried another one:
---------------------------------------------------------
SELECT COUNT (*) AS UNSTRUCTURED
FROM data INNER JOIN elements ON data.ownerid=elements.id
WHERE (data.typeid=19 AND data.value>7) AND data.ownerid IN
(SELECT data.ownerid FROM data WHERE (data.typeid=18 AND data.value<=10)
AND elements.typeid=4);
---------------------------------------------------------
Here is one count, I use a UNION to get the 4 numbers but my result is one column and I'd like to catch a row in order to make a graphical analysis with Access. I think the &quot;sum case&quot; would give a row. So i need a mix of these structures, thanx for your help if you have any idea...

 

[carp]

How about

SELECT a.both, b.c1, c.c2, d.neither
FROM
(SELECT count(*) both FROM my_table WHERE cr1 < gate AND cr2 < gate) a,
(SELECT count(*) c1 FROM my_table WHERE cr1 < gate AND cr2 >= gate) b,
(SELECT count(*) c2 FROM my_table WHERE cr1 >= gate AND cr2 < gate) c,
(SELECT count(*) neither FROM my_table WHERE cr1 >= gate and cr2 >= gate) d;

This will work in Oracle; I don't know about other RDBMS.

Of course, this has the disadvantage of actually querying the table four times instead of just once, but it returns all of your results in one set.

 

[tlbroadbent]

If you are doing the queries in Access, then CASE will not work. CASE is an ANSI standard construct but Access doesn't adhere to the standard. It would have been better to post your question in an Access specific forum such as &quot;Microsoft: Access Queries and Access/SQL.&quot;

In place of teh CASE statement you can use the IIF function in Access.

Select
Sum(
IIF(data.typeid=19 AND data.value>7 AND data.ownerid IN (SELECT data.ownerid FROM data WHERE data.typeid=18 AND data.value>10 AND elements.typeid=4), 1, 0)) As CRITICAL Terry Broadbent
Please review faq183-874.

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[pondi]

Thank you 2 guys, I have tried the 2 structures you propose: the one of carp doesn't seem to work, I have a FROM error, i have not tried to debug it because I think this is an Access conflict. The one of tlbroadbent seemed to work because when I ran it on an empty DB i had the 4 columns required. But when i test it on a piece of my db (about 80 000 rows in data table), i can't see the end.
So I still have my one-row issue, i may try an access forum, sorry for the mistake but i'm used to work with SQLS7 and I already knew this forum well.
Thanx a lot for your help.