Results 1 to 9 of 9

Thread: Pl/sql problem

  1. #1
    Join Date
    Feb 2003
    Posts
    3

    Unanswered: Pl/sql problem

    Problem with PL/SQL
    I want to use the count aggregate function on 3 columns within the same table and write out their answers uisng dbms output.

    ex. Table pal
    ----------------
    p1, p2, p3, p4 as columns.

    select pl, count(*)amount
    from pal
    group by pl;

    but the next select sats does not allow me......

    Immediate help on this will be highly appreciated.

    /Babsco

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

    Re: Pl/sql problem

    Originally posted by Babsco
    but the next select sats does not allow me......
    /Babsco
    Sorry, what does this mean? Please whow what you are doing, and what error message you are getting (cut and paste from SQL Plus).

  3. #3
    Join Date
    Feb 2003
    Posts
    3

    Re: Pl/sql problem

    Originally posted by Babsco
    Problem with PL/SQL
    I want to use the count aggregate function on 3 columns within the same table and write out their answers uisng dbms output.

    ex. Table pal
    ----------------
    p1, p2, p3, p4 as columns.

    select pl, count(*)amount
    from pal
    group by pl;

    but the next select sats does not allow me......

    Immediate help on this will be highly appreciated.

    /Babsco
    If I write:
    select pl, count(*)as amount
    from pal
    group by pl;
    The above statement works fine with no problem. But I would like to make a
    similar selection statement for the next column as well.
    like: select p2, count(*) as amount2
    from pal
    group by pl;
    How can I join two select satements together, so that I can use count()
    agregate on two columns of the same table? Should I use union all or?

    select pl, count(*)as amount
    from pal
    group by pl;

    select p2, count(*) as amount2
    from pal
    group by pl;

  4. #4
    Join Date
    Feb 2003
    Posts
    3
    Try:

    select pl, p1, count(*) as amount
    from pal
    group by pl, p1;

  5. #5
    Join Date
    Feb 2003
    Posts
    3

    Red face

    sorry that was meant to be p1, p2 (ignore pl)

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

    Re: Pl/sql problem

    Yes, UNION ALL will give you a combined result like:

    a 10
    b 20
    c 20
    x 10
    y 10

    (where a,b,c are values of p1 and x,y are values of p2)

    Is that what you want?

  7. #7
    Join Date
    Feb 2003
    Posts
    3

    Re: Pl/sql problem

    Originally posted by andrewst
    Yes, UNION ALL will give you a combined result like:

    a 10
    b 20
    c 20
    x 10
    y 10

    (where a,b,c are values of p1 and x,y are values of p2)

    Is that what you want?

    Yes Andrew that is correct and I did try it already this morning and
    it worked fine. I had as well to skip the ; after every group by keyword.
    Thanks for the help

    Cheers!
    Babsco

  8. #8
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    27

    Re: Pl/sql problem

    Hi, I read your reply to a post entitled: Pl/sql problem.

    I am trying to do something similar. I have already managed to print out the column name and the count of matches using UNION All.

    Output is:
    TEAM ASTON_VILLA_GAMES_LEFT
    ---------- ----------------------
    Charlton 1
    Liverpool 0
    Arsenal 0
    Chelsea 1

    What I want to do now is to get the total for the 4 outputs. eg:

    TEAM ASTON_VILLA_GAMES_LEFT
    ---------- ----------------------
    Charlton 1
    Liverpool 0
    Arsenal 0
    Chelsea 1
    2
    Any imediate help will be appreciated.
    Many thanks,

    ** code **
    BEGIN

    select 'Charlton ' as Team, count(opponent) AS Aston_Villa_Games_Left
    from charlton_games@charltonlink
    where opponent = 'Aston Villa' AND match_date > sysdate
    union all
    select 'Liverpool ' as Team, count(opponent) AS Villa_Games_Left
    from liverpool_games@liverpoollink
    where opponent = 'Aston Villa' AND match_date > sysdate
    union all
    select 'Arsenal ' as Team, count(opponent) AS Villa_Games_Left
    from arsenal_games@arsenallink
    where opponent = 'Aston Villa' AND match_date > sysdate
    union all
    select 'Chelsea ' as Team, count(opponent) AS Villa_Games_Left
    from chelsea_games@chelsealink
    where opponent = 'Aston Villa' AND match_date > sysdate;


    END

    Ahmed

    Originally posted by andrewst
    Yes, UNION ALL will give you a combined result like:

    a 10
    b 20
    c 20
    x 10
    y 10

    (where a,b,c are values of p1 and x,y are values of p2)

    Is that what you want?
    the one and only ada17

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

    Re: Pl/sql problem

    In SQL Plus you can do this:

    COMPUTE SUM OF aston_villa_games_left ON REPORT
    BREAK ON REPORT

    Then run your query again.

Posting Permissions

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