Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2008
    Posts
    17

    Unanswered: Using COUNT, but trouble filtering results

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try
    Code:
    WHERE  some_field = 0
    Where 0 = False, 1 = True
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2008
    Posts
    17
    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

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try grouping by the other field as well
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2008
    Posts
    17
    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

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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.
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2008
    Posts
    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.

  8. #8
    Join Date
    Nov 2002
    Posts
    128
    Provided Answers: 1
    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

  9. #9
    Join Date
    Apr 2008
    Posts
    17
    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 . . .

  10. #10
    Join Date
    Apr 2008
    Posts
    17
    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 . . .

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  12. #12
    Join Date
    Apr 2008
    Posts
    17
    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:
    Learning <> ingnorance
    I must be learning - the joke wasn't lost on me!!

  13. #13
    Join Date
    Apr 2008
    Posts
    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?
    Attached Thumbnails Attached Thumbnails Output.jpeg  

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •