1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Column wise count

Discussion in 'SQL PL/SQL' started by jagadekara, May 30, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    Somebody asked me a question like below.

    We need count of each column from a table, but we don't want give column name each time.

    Select count(segment1),count(creation_date).... from po_headers_all

    po_headers_all have lot of columns, so to give all column names will take long time.

    So without mentioning column name every time, we need count of every column from that table.

    Is it possible?
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    I tried like this....
    SELECT count(select column_name from all_tab_cols
    where table_name='PO_HEADERS_ALL') FROM po_headers_all ;

    But it's giving error...

    Then I tried like this...

    declare
    p_cola varchar2(100);
    cursor c1 is select column_name from all_tab_cols
    where table_name='PO_HEADERS_ALL';
    begin
    for i in c1 loop
    select count(i.column_name) into p_cola from po_headers_all;
    dbms_output.put_line(i.column_name);
    dbms_output.put_line('---------------------------------');
    dbms_output.put_line(p_cola);
    end loop;
    end;

    It is giving total records count for all column, not giving individual column count.
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Jagadekara,

    SELECT count(column_name) will count nun null occurrences of the named column from the table.

    If all the columns are filled, then the count would return same value for all.
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    Just to illustrate above with example code (always better than just words ;)):

    Code (SQL):
    CREATE TABLE club_orcl (
    n1 NUMBER,
    n2 NUMBER,
    n3 NUMBER,
    n4 NUMBER
    );

    INSERT INTO club_orcl VALUES (1,10,100,NULL);
    INSERT INTO club_orcl VALUES (2,20,NULL,NULL);
    INSERT INTO club_orcl VALUES (3,30,NULL,NULL);

    COMMIT;

    SQL> SELECT COUNT(n1), COUNT(n2), COUNT(n3), COUNT(n4) FROM club_orcl;

     COUNT(N1)  COUNT(N2)  COUNT(N3)  COUNT(N4)
    ---------- ---------- ---------- ----------
             3          3          1          0
     
    Hope this helps in the comprehension of COUNT and assist you in what you're trying to achieve.
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    After running your script, I understood your problem: you cannot execute a SELECT COUNT in this way to get the individual column count.

    The statement "SELECT COUNT(i.column_name)" is interpreted as "SELECT COUNT(N1)..." (for example) where "N1" is not considered a column name but a value (constant) same way as "SELECT COUNT(1)" or "SELECT COUNT(*)" which return the total no. of rows in the table.

    In order to achieve what you're trying to, you need to use dynamic SQL:

    Code (SQL):
    DECLARE
    p_cola varchar2(100);
    cursor c1 IS SELECT column_name FROM all_tab_cols
    WHERE TABLE_NAME='PO_HEADERS_ALL';
    BEGIN
    FOR i IN c1 loop
    EXECUTE IMMEDIATE 'select count('||i.column_name||') from PO_HEADERS_ALL'
       INTO p_cola;
    dbms_output.put_line(i.column_name);
    dbms_output.put_line('---------------------------------');
    dbms_output.put_line(p_cola);
    END loop;
    END;
    /
     
     
    jagadekara likes this.
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Rajen,

    This is what I exactly want....
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Another choice...

    SELECT table_name,column_name,
    TO_NUMBER (
    EXTRACTVALUE (
    xmltype (
    DBMS_XMLGEN.getxml ('select count('||column_name||') c from ' || 'PO_HEADERS_ALL')),
    '/ROWSET/ROW/C'))
    COUNT
    FROM all_tab_cols
    where table_name ='PO_HEADERS_ALL' -------> substitute table name
    ORDER BY column_id;