Using COUNT, but trouble filtering results

Zeedok · #1 (**permalink**) 09-01-08, 05:06

I have built a simple database using OoBase and am having trouble writing some SQL queries. I am trying to work out how to select a group of patients from my database (eg ones that have responded to a treatment - YES/NO field) then count how many have a particular clinical characteristic (one of three VARCHAR entries). Both fields are in the same table. I can use SELECT and COUNT to count how many have the clinical characteristic, but I can't get the WHERE command (which I am trying to use to filter) to work.

Here's my query:

Code:

SELECT "Distal>Prox", COUNT( "Distal>Prox" ) AS "Distal weakness" FROM "New mega table" WHERE NotsureIVIG = FALSE GROUP BY "Distal>Prox"

Any ideas on what's wrong - or is there a better way to do this??

Thanks

gvee · #2 (**permalink**) 09-01-08, 05:13

Try

Code:

WHERE  some_field = 0

Where 0 = False, 1 = True

Zeedok · #3 (**permalink**) 09-01-08, 06:40

Awesome. Works perfectly, thanks.

Is there a way to have two queries in the same output eg:

Reponders Non-responders

Proximal 5 12
Distal 12 3
Mixed 4 8

If I use:

Code:

SELECT "Distal>Prox", COUNT( "Distal>Prox" ) AS "Pattern_of_Weakness" FROM "New mega table" WHERE "Responded IVIG" = 1 GROUP BY "Distal>Prox"

Then I can get the count for one group, but how can I get the count for both at once (ie without re-writing the query with WHERE other_field = 0)?

Thanks

gvee · #4 (**permalink**) 09-01-08, 06:48

Try grouping by the other field as well

Zeedok · #5 (**permalink**) 09-01-08, 07:03

Not sure what you mean. I want to have two columns (I guess) one with WHERE some_field = 0 and the other WHERE some_field = 1, but divided into groups. How should I edit my query to try what you've suggested?

Thanks

gvee · #6 (**permalink**) 09-01-08, 07:07

2 options

Code:

SELECT col1
     , some_field
     , Count(*) As [number]
FROM   my_table
GROUP
    BY col1
     , some_field


SELECT col1
     , Sum(Iif(some_field = 1, 1, 0)) As [true]
     , Sum(Iif(some_field = 0, 1, 0)) As [false]
FROM   my_table
GROUP
    BY col1

Note that these queries provide seemingly different results.

Zeedok · #7 (**permalink**) 09-02-08, 06:17

Sorry - you've lost me. I know zero SQL, that's why I'm asking for help here. Should your formatting be followed exactly (ie on separate lines)? When you write 'some_field' in several places, should it be the same 'some_field' each time? Can you maybe hi-light the parts of your query that I need to change (with my details) to get it to work??

Thanks for your patience - sorry I'm so ignorant.

jrbbldr · #8 (**permalink**) 09-02-08, 09:49

Each language has its own SQL Query commands which it supports and I am not familiar with yours. But if this were in Visual Foxpro I would use the following commands:
SUM() && for summation
IIF() && conditional IF command

Example:
SELECT <something>,;
SUM(IIF(TestResult = "IVIG",1,0)) as IVIG,;
SUM(IIF(TestResult = "Other",1,0)) as Other;
FROM MyResults;
WHERE <SelectionCriteria>

The above would Sum a 1 for each record meeting the IVIG criteria and 0 if not - thereby resulting in a COUNT for this condition.
And it would SUM a 1 for each record meeting the OTHER criteria and a 0 if not - thereby resulting in a COUNT for this condition.

Note that my example uses some 'generic' statements such as <something> or <SelectionCriteria> indicating that it can also be whatever you need it to be.

If your language does not support the IIF() command other languages typically have equivalents such as CASE().

Also note that if you have used some obscure language for your application and data, you are limiting the number of individuals who can offer you meaningful assistance.

Good Luck

Zeedok · #9 (**permalink**) 09-02-08, 17:54

Thanks for your help. I haven't had a chance to try your suggestions out yet, but once I do I'll let you know what happens.

When you refer to 'Languages' . . . my understanding (please correct me if I am wrong) is that each database program uses it's own SQL variants to write queries. So once you've chosen a particular program, you have to use that variant of SQL - is that right??

Since I am using ooBase, I'll need to use SQL commands that work for ooBase. Here's a link to one of their wiki pages with their SQL commands:

HTML Code:

http://wiki.services.openoffice.org/wiki/Built-in_functions_and_Stored_Procedures

I had a quick look and couldn't find the IIF() function . . .

Zeedok · #10 (**permalink**) 09-03-08, 06:39

I've done a little more reading. Oobase uses the HSQL database engine my default. I am happy with the following query (which works fine):

Code:

SELECT "Distal>Prox",
COUNT ("Distal>Prox") AS "Responders"
FROM "New mega table"
WHERE "Responded IVIG" = TRUE
GROUP BY "Distal>Prox"

and selecting the Non-responders with:

Code:

SELECT "Distal>Prox",
COUNT ("Distal>Prox") AS "Non-Reponders"
FROM "New mega table"
WHERE "Responded IVIG" = FALSE
GROUP BY "Distal>Prox"

But I would like to have one query to combine these two . . . I'm just not sure how.

I have tried (I think its correct) Georgev's suggestion:

Code:

SELECT "Distal>Prox", 
SUM (IIF("Responded IVIG" = 1, 1, 0)) As [true]
SUM (IIF("Responded IVIG" = 0, 1, 0)) As [false]
FROM "New mega table"
GROUP BY "Distal>Prox"

But it didn't work. I can't find any references to the SUM IIF command - is it recognised by ooBase?? Is there a different, but equivalent command I could try?

Thanks for your help - I am learning . . .

gvee · #11 (**permalink**) 09-03-08, 06:46

Quote:

Originally Posted by Zeedok

When you refer to 'Languages' . . . my understanding (please correct me if I am wrong) is that each database program uses it's own SQL variants to write queries. So once you've chosen a particular program, you have to use that variant of SQL - is that right??

That is correct. Even though standards have been defined, you'll find proprietary syntax in every vendors offering. Think about how HTML & CSS rendor in different browsers (IE, FF, Opera, etc) as an example of this concept.

Thanks for the link on OObase (and for telling us the platform you're using!) - I've had a quick look and can't find on it what I was looking for either - I'd love to see a page which just showed the SQL syntax available! IIf() is something that is used in Microsoft products, specifically Excel and Access, so as you've found it, is more than likely proprietary to those applications.

Have a try with CASE

Code:

SELECT col1
     , Sum(CASE WHEN some_field = 1 THEN 1 ELSE 0 END) As [true]
     , Sum(CASE WHEN some_field = 0 THEN 1 ELSE 0 END) As [false]
FROM   my_table
GROUP
    BY col1

Also, have you tried this badger

Code:

SELECT col1
     , some_field
     , Count(*) As [number]
FROM   my_table
GROUP
    BY col1
     , some_field

This should work, but as suggested before, will return a slightly different looking resultset, but nevertheless all the information is in there!

Quote:

Originally Posted by Zeedok

Sorry - you've lost me. I know zero SQL, that's why I'm asking for help here. Should your formatting be followed exactly (ie on separate lines)? When you write 'some_field' in several places, should it be the same 'some_field' each time? Can you maybe hi-light the parts of your query that I need to change (with my details) to get it to work??

The formatting is just the style I implement - I believe it makes the query more readable. At the risk of repeating an analogy, think of it in the same way that whitespace is treated in HTML: multiple spaces are removed by the compiler and replaced with a single space - no point in sending redundant data to something that doesn't care if what it recieved is tabbed out in a nice way!

Your assumption that "some_field" is the same field throughout the query is correct; had there been another field involved, I'd probably have gone with "some_other_field" as it's name

Quote:

Originally Posted by Zeedok

Thanks for your patience - sorry I'm so ignorant.

Learning <> ingnorance

Hope this helps

Zeedok · #12 (**permalink**) 09-03-08, 07:08

CASE doesn't seem to work. I tried this:

Code:

SELECT "Distal>Prox"
     , Sum (CASE WHEN "Responded IVIG" = 1 THEN 1 ELSE 0 END) As [true]
     , Sum (CASE WHEN "Responded IVIG" = 0 THEN 1 ELSE 0 END) As [false]
FROM "New mega table"
GROUP BY "Distal>Prox"

to no avail.

I also tried your other suggestion:

Code:

SELECT "Distal>Prox"
     , "Responded IVIG"
     , Count(*) As [1]
FROM   my_table
GROUP BY "Distal>Prox", "Responded IVIG"

But I may have misunderstood it. Thanks for the SQL humour:

Quote:

Learning <> ingnorance

I must be learning - the joke wasn't lost on me!!

Zeedok · #13 (**permalink**) 09-03-08, 07:17

I got your second suggestion to work, it may have been the square brackets that were a problem. Here's the code that worked:

Code:

SELECT "Distal>Prox", "Responded IVIG", Count("Distal>Prox") As "Numbers of Patients"
FROM "New mega table"
GROUP BY "Responded IVIG", "Distal>Prox"

and I have attached my output. If I could get your first suggestion to work, would the output be easier to read?

gvee · #14 (**permalink**) 09-03-08, 08:55

My bad, the square brackets are also propriatory and not standard syntax (I got caught out on this last week: see the end of this post)

Without knowing the equivalent in OObase for If(), Iif() or CASE, I'm unable to hel you find the "easier to read" solution, however I can help you underestand the logic involved to get the answer.

Look back at this bit of code

Code:

Sum (CASE WHEN "Responded IVIG" = 0 THEN 1 ELSE 0 END) As [false]

What is it doing?
I don't want to break it down for you just yet; I want to see if you understand the logic involved and why this should give you the answer you need.

Quote:

Originally Posted by Zeedok

Code:

FROM "New mega table"

I like your SQL humour too

gvee · #15 (**permalink**) 09-03-08, 08:57

P.S. As a side note, you should really try and avoid putting spaces in your field and table names. This is a whole different topic so I won't persue it here.

Just next time you start a system, don't be afraid to come back and post your initial thoughts on table design etc in the appropriate topic on dbforums

Internet Services

Web Development

Digital Marketing

Consumer Tech