Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    83

    Unanswered: SQL Count problem

    Hi,

    I have 3 databases and all have a field called "Type". What I am trying to do it get a total count for all the rows in each table, but in one query.

    I would like to avoid having 3 separate querys like this where I would just change the FROM statement:

    SELECT count(Type)
    FROM table1


    Here are the values for the column

    table1: Type = 'transaction'
    table2: Type = 'request'
    table3: Type = 'rejection'

    Can this be done in one big query?

  2. #2
    Join Date
    Sep 2003
    Posts
    156

    Re: SQL Count problem

    Originally posted by turk99
    Hi,

    I have 3 databases and all have a field called "Type". What I am trying to do it get a total count for all the rows in each table, but in one query.

    I would like to avoid having 3 separate querys like this where I would just change the FROM statement:

    SELECT count(Type)
    FROM table1


    Here are the values for the column

    table1: Type = 'transaction'
    table2: Type = 'request'
    table3: Type = 'rejection'

    Can this be done in one big query?
    do you mean like...

    select count(t1.type), count(t2.type), count(t3.type)
    from table1 t1, table2 t2, table3 t3;

    COUNT(T1.TEST) COUNT(T2.TEST) COUNT(T3.TEST)
    -------------- -------------- --------------
    1 1 1
    rgs,

    Ghostman

  3. #3
    Join Date
    Jan 2004
    Posts
    83

    Re: SQL Count problem

    Won't that return a sum total for all three? I need an individual count for each table.

    So Table1 has 10 records, Table2 had 14, and Table3 has 12.

  4. #4
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120

    Re: SQL Count problem

    Originally posted by turk99
    Won't that return a sum total for all three? I need an individual count for each table.

    So Table1 has 10 records, Table2 had 14, and Table3 has 12.
    GhostMan is correct.
    Give it a try,
    select count(t1.type), count(t2.type), count(t3.type)
    from table1 t1, table2 t2, table3 t3;

  5. #5
    Join Date
    Jan 2004
    Posts
    83

    Re: SQL Count problem

    Ok, I tried that and what happened was it multiplied the results of each column. So now I have 3 results that both say 144 (9*4*4)

  6. #6
    Join Date
    Jan 2004
    Posts
    9
    try this, select in from clause

    select TAB1.SUM_TAB1 + TAB2.SUM_TAB2
    from
    (
    SELECT count(*) SUM_TAB1 FROM ASDF
    ) TAB1
    ,
    (
    SELECT count(*) SUM_TAB2 FROM QWER
    ) TAB2

  7. #7
    Join Date
    Jan 2004
    Posts
    83
    that's seems to have worked. now i'll see if it's more effiecent than having 3 separate queries.

    thanks for the help!

  8. #8
    Join Date
    Jan 2004
    Posts
    1
    Originally posted by turk99
    that's seems to have worked. now i'll see if it's more effiecent than having 3 separate queries.

    thanks for the help!

    Hi,

    How about using UNION or UNION ALL.

    select count(name) from name1
    union
    select count(name) from name2
    union
    select count(name) from name3;

    same with union all as well

    select count(name) from name1
    union all
    select count(name) from name2


    Could anyone please let me know what difference would it make(i am new to oracle).

  9. #9
    Join Date
    Jan 2004
    Location
    Leiden, The Netherlands
    Posts
    11
    If you want to count each table use:

    select t1,t2,t3
    from (select count(*) t1 from table_name1)
    , (select count(*) t2 from table_name2)
    , (select count(*) t3 from table_name3);

    This will give you 1 record with 3 columns.

    or

    select count(*) from table_name1
    union all
    select count(*) from table_name2
    union all
    select count(*) from table_name3;

    This will give 3 records with 1 column.

    Don't use 'union', use 'union all' because 'union' deletes duplicate lines.


    select 1 from dual
    union
    select 1 from dual
    /

    1
    ----------
    1

    (deletion of duplicates)

    select 1 from dual
    union all
    select 1 from dual
    /

    1
    ----------
    1
    1

    (no deletion of duplicates)



    If you want to get the total number of records of the 3 tables do:

    select t1+t2+t3
    from (select count(*) t1 from table_name1)
    , (select count(*) t2 from table_name2)
    , (select count(*) t3 from table_name3);

Posting Permissions

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