Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Unanswered: Possible to do this in one query..?

    Hi all. I have a quick question... Given this table..

    Code:
    CREATE TABLE SAMPLE_DATA ("X" CHAR(5) NOT NULL, "Y" 
    NUMBER(10) NOT NULL, "ID" NUMBER(10) NOT NULL)  ;
    
    Insert Into sample_data (x, y) values ('a',11,1);
    Insert Into sample_data (x, y) values ('b',11,2);
    Insert Into sample_data (x, y) values ('b',11,3);
    Insert Into sample_data (x, y) values ('c',11,4);
    Insert Into sample_data (x, y) values ('c',11,5);
    Insert Into sample_data (x, y) values ('c',11,5);
    Is there a way to find out to obtain each type of field A (a, b, c) and count how many times each has occured in one query?

    Output example:
    x y
    _____
    a 1
    b 2
    c 3



    Currently I'm doing somethin like..

    Select distinct x from sample_data
    and then looping through... (in my application...)
    select count (x) from sample_data where y = 11;

    not really too efficient...any help would be great

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    select x, count(1) from sample_data group by x

  3. #3
    Join Date
    Dec 2003
    Posts
    148
    Hey, thank you for the response....That definitely works, I just have a question...

    WHat does the ..."count (1)" represent...Specifcally, the 1...Thank you for your help

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by RhythmAddict
    WHat does the ..."count (1)" represent...Specifcally, the 1...
    It's just a constant. For every row that satisfies the WHERE condition there will be a 1 in the result set, so every row will be counted. If you specify "count(column_1)" rows that contain nulls won't be counted. You probably can also say "count(*)" but in the good ol' days optimizers sometimes would not realize that all you needed was a row count and would retrieve all columns from a table, which could affect performance. These days I guess "count(1)" and "count(*)" have the same effect.

  5. #5
    Join Date
    Dec 2003
    Posts
    148

    gotcha..

    thank you 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
  •