CODEWITHSUNDEEP
sqloracle-databaseselect
gymcode
gymcode

Reputation: 4623

Oracle Database SQL Query

I am currently running a SQL statement to retrieve `total prize money earned by competitors of every country'

However, I am currently hardcoding the country names in my SELECT statement as follows:

SELECT C2.NATIONALITY, SUM(C2.TOTALPRIZEMONEY) AS TOTALPRIZEMONEY
FROM COMPETITOR C2, COMPETITION C1
WHERE NATIONALITY IN ('USA','AUSTRALIA','SINGAPORE')
AND C1.TIMEPLANNED BETWEEN TO_DATE('01-JAN-15') AND TO_DATE ('31-DEC-15')
GROUP BY NATIONALITY;

May I know how can I remove the part that declares the country name in the statement, and to use a query to retrieve all countries available?

Below is my table structure:

enter image description here

Upvotes: 1

Views: 193

Answers (4)

HAYMbl4
HAYMbl4

Reputation: 1470

If columns COMPETITOR.NATIONALITY and COMPETITION.COUNTRY conaits several information you can use follow query:

select cr.nationality,
    sum(cr.totalprizemoney)
from competitor cr
where cr.nationality in
    (
        select cn.country
        from competition cn
        where cn.timeplanned between to_date('01-JAN-15') and to_date ('31-DEC-15')
    )
group by cr.nationality;

Upvotes: 1

Avrajit Roy
Avrajit Roy

Reputation: 3303

Youo can try this code. As assuming there is no other table which has proper mapping for Nationality below code will only fetch distinct Nationality present in COMPETITOR Table.

SELECT C2.NATIONALITY,
  SUM(C2.TOTALPRIZEMONEY) AS TOTALPRIZEMONEY
FROM COMPETITOR C2,
  COMPETITION C1
WHERE NATIONALITY IN
  (SELECT DISTINCT c3.NATIONALITY FROM COMPETITOR c3
  ) -- Assuming there is not separate table/mapping present for this
AND C1.TIMEPLANNED BETWEEN TO_DATE('01-JAN-15') AND TO_DATE ('31-DEC-15')
GROUP BY NATIONALITY;

Upvotes: 1

Eduard Uta
Eduard Uta

Reputation: 2607

You can use the competition table (country column) to get all potential countries (and get distinct values using group by).

SELECT C2.NATIONALITY, SUM(C2.TOTALPRIZEMONEY) AS TOTALPRIZEMONEY
FROM COMPETITOR C2, COMPETITION C1
WHERE NATIONALITY IN (SELECT COUNTRY FROM COMPETITION GROUP BY COUNTRY)
AND C1.TIMEPLANNED BETWEEN TO_DATE('01-JAN-15') AND TO_DATE ('31-DEC-15')
GROUP BY NATIONALITY;

Upvotes: 2

Jagdish Patil
Jagdish Patil

Reputation: 21

Here in both the tables I can not see any foreign key defined for the tables. There must be some other table which will contains mapping. If it is not there then, you will not be able to get correct output as it will give you cross product.

Upvotes: 2

Related Questions