Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Posts
    50

    Unanswered: Obtaining total number of rows

    Hi!

    How can I obtain the number of rows returned in the same query that gets the data. Is it possible?

    If I have

    Code:
    SELECT chaName, creatDate, chaImage, COUNT(*) total FROM Channel 
    GROUP BY chaName, creatDate, chaImage, isPublic
    HAVING isPublic = 1  
    ORDER BY chaName
    total will be 1 for each row. I would like to get the total number of rows. Do I have to do it in two queries?

    Thanks.
    Ah! Não ser eu toda a gente e toda a parte!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT chaName, creatDate, chaImage, COUNT(isPublic) total
    FROM Channel
    GROUP BY chaName, creatDate, chaImage
    HAVING isPublic = 1
    ORDER BY chaName

  3. #3
    Join Date
    Dec 2003
    Posts
    50
    Sorry, if I do that I get the error it's not an expression GROUP BY.

    If I put isPublic there

    Code:
    SELECT chaName, creatDate, chaImage, COUNT(isPublic) total 
    FROM Channel 
    GROUP BY chaName, creatDate, chaImage, isPublic
    HAVING isPublic = 1 
    ORDER BY chaName
    I don't get the error but I still get the column total with the value 1 in each field. It doesn't count the rows I get.
    Ah! Não ser eu toda a gente e toda a parte!

  4. #4
    Join Date
    Jan 2004
    Location
    India
    Posts
    62
    SELECT chaName, creatDate, chaImage, isPublic, count(*) total
    FROM Channel
    GROUP BY chaName, creatDate, chaImage, isPublic
    ORDER BY chaName

    what about this ? And by the way what type of output you want ? Please give a sample of output you want.
    Regards,

    Rushi

  5. #5
    Join Date
    Dec 2003
    Posts
    50
    I want to get the chaName, creatData and chaImage atributes (rows with three columns) and, if possible, the total number of rows returned. I don't want to select isPublic but all rows selected must have isPublic = 1.

    So, your solution becomes my first one:

    Code:
     
    SELECT chaName, creatDate, chaImage, COUNT(*) total FROM Channel 
    GROUP BY chaName, creatDate, chaImage, isPublic
    HAVING isPublic = 1  
    ORDER BY chaName
    This gets something like.

    CHANAME | CREATDATE | CHAIMAGE | TOTAL
    ------------------------------------------
    Chill-Out | 03.09.15 | chillout.jpg | 1
    Fado | 04.01.12 | fado.jpg | 1
    outro | 03.08.14 | | 1
    Rock | 03.08.14 | rock.jpg | 1
    Techno | 04.01.15 | techno.jpg | 1

    I don't need a column total with a value 1 for each row. I want to get the total number of rows returned (in this case 5) with the results. Can I do it?

    Thanks.
    Ah! Não ser eu toda a gente e toda a parte!

  6. #6
    Join Date
    Sep 2003
    Posts
    156

    Re: Obtaining total number of rows

    [QUOTE][SIZE=1]Originally posted by rpOliveira
    Hi!
    Last edited by GhostMan; 01-16-04 at 09:52.
    rgs,

    Ghostman

  7. #7
    Join Date
    Sep 2003
    Posts
    156

    Re: Obtaining total number of rows

    Originally posted by rpOliveira
    Hi!

    How can I obtain the number of rows returned in the same query that gets the data. Is it possible?

    If I have

    Code:
    SELECT chaName, creatDate, chaImage, COUNT(*) total FROM Channel 
    GROUP BY chaName, creatDate, chaImage, isPublic
    HAVING isPublic = 1  
    ORDER BY chaName
    total will be 1 for each row. I would like to get the total number of rows. Do I have to do it in two queries?

    Thanks.
    Please ignore the last post, i sent whilst thinking...


    SQL>set serveroutput on
    SQL>DECLARE
    SQL>v_count NUMBER;
    SQL>CURSOR c1 is SELECT chaName, creatDate, chaImage, total
    SQL>FROM Channel
    SQL>ORDER BY chaName;
    SQL>v_name c1%ROWTYPE;
    SQL>BEGIN
    SQL>COMMIT;
    SQL>SELECT COUNT(*) INTO v_count FROM Channel;
    SQL>OPEN c1;
    SQL>LOOP
    SQL>FETCH c1 INTO v_name;
    SQL>EXIT WHEN c1%NOTFOUND;
    SQL>DBMS_OUTPUT.PUT_LINE(v_name.chaName||' '||v_name.creatDate||' '||v_name.chaImage||' '||v_name.total);
    SQL> end loop;
    SQL>DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || ' rows found.');
    SQL> COMMIT;
    SQL>END;
    SQL> /

    There we go... sorry about the mess i made a minute ago... pressed submit by mistake...
    rgs,

    Ghostman

  8. #8
    Join Date
    Jan 2004
    Posts
    1
    Tis query gives you all single values and the total nr of records.

    SELECT chaName, creatDate, chaImage, (SELECT COUNT(*) FROM Channel WHERE isPublic = 1 ) AS total FROM Channel
    WHERE isPublic = 1
    ORDER BY chaName

  9. #9
    Join Date
    Dec 2003
    Posts
    50
    Thanks a lot guys.

    GhostMan, you can delete or edit a post already submited by the link under the post in the thread visualization.

    Rendem's solution is much more simple and I can even take out "WHERE isPublic = 1" to have it working.

    Code:
    SELECT chaName, creatDate, chaImage, (SELECT COUNT(*) FROM Channel ) AS total FROM Channel 
    WHERE isPublic = 1 
    ORDER BY chaName
    Thanks for your attention.
    Ah! Não ser eu toda a gente e toda a parte!

Posting Permissions

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