Oracle Select and Group By

Question

I have the below table.

create table cricket(team1 varchar2(22),team2 varchar2(22));

select * from cricket

Team1 Team2

INDIA  SL
SL    AUS
SA    ENG
ENG   NZ
AUS   INDIA

in the above table i want to display the count of teams played. for example

India 2
SL    1
Sa    1
Eng   2
Aus   2

Answer 1

You can UNION ALL the two columns, then count over that:

WITH ALL_TEAMS AS (
    SELECT TEAM1 AS TEAM FROM CRICKET
    UNION ALL 
    SELECT TEAM2 FROM CRICKET
)
SELECT 
    TEAM, 
    COUNT(1) AS NUMBER_OF_PLAYS
FROM ALL_TEAMS
GROUP BY TEAM