[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

[andrewst]

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).

[Babsco]

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;

[natalia]

Try:

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

[natalia]

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

[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?

[Babsco]

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

[ada17]

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?

[andrewst]

In SQL Plus you can do this:

COMPUTE SUM OF aston_villa_games_left ON REPORT
BREAK ON REPORT

Then run your query again.