Results 1 to 9 of 9

Thread: count distinct

  1. #1
    Join Date
    Oct 2003
    Posts
    22

    Unhappy Unanswered: count distinct

    Hi... hereby i hv a problem

    At my database, I hv a field "component_detail_key",
    and the data is :
    T002811_1
    T002811_2
    T002812_1
    T002812_2
    T002813_1
    T002813_2

    I get the data before _ :
    T002811
    T002811
    T002812
    T002812
    T002813
    T002813

    Now I hv to count distinct, the output should be 3.
    I use 2 for loop in this function but can't do that.
    Below is my coding :

    int distinct = 0;

    for (int i = 1; i < a; i++){
    for (int j = 1; j < a; j++)
    {
    if (swkey[i]==swkey[j])
    {
    distinct = distinct;
    } else {
    distinct ++;
    }
    } // End for
    }

    The answer is :5 , 10 , 15 , 20 , 25 , 30

    Anyone can help???pls~

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: count distinct

    Why don't you just do this in SQL something like this:

    SELECT COUNT( DISTINCT( SUBSTR( code, 1, INSTR(code,'_')-1 )))
    FROM table
    ...;

    I'm using Oracle SUBSTR and INSTR functions, but most DBMSs have something similar.

  3. #3
    Join Date
    Oct 2003
    Posts
    22

    Smile

    May I know what is the meaning for :INSTR(code,'_')-1?
    The "code" is field or...?

    Thx~

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    INSTR(string1, string2) returns the position of string2 within string1. Therefore, SUBSTR(string1, 1, INSTR(string1, string2)-1) will return a new string containing all the chacracters of string1, prior to the occurence of string2.

    Example,

    String1('123456');
    String2('4');

    SUBSTR(string1, 1, INSTR(string1, string2)-1) = "123"
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Oct 2003
    Posts
    22

    Smile

    oh i c~
    Thanks very much!!!

  6. #6
    Join Date
    Oct 2003
    Posts
    22
    BUt still hv error : invalid command for INSTR...
    I am using jsp... is it can't support this code?

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I would recommend that the DBMS perform the complete operation.

    Java:
    IndexOf(string)
    Last edited by r123456; 02-24-04 at 00:32.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Oct 2003
    Posts
    22
    sorry not understand this......
    I am fresh with Java

    is it
    String sqlsw = "SELECT SUBSTRING_INDEX(component_detail_key, '_', 1) AS sw FROM component_detail WHERE component_key=?";
    ?
    but still can't....
    Last edited by winterain; 02-24-04 at 02:04.

  9. #9
    Join Date
    Oct 2003
    Posts
    22
    I solve the problem by CHARINDEX,
    thx very much for your help

Posting Permissions

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