Oracle Database SQL Query

Question

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:

in which table do you have the country. I you know that you can use NATIONALITY IN (select country from table) — PK20
– PK20, Commented Nov 27, 2015 at 7:16
SELECT C2.NATIONALITY, SUM(C2.TOTALPRIZEMONEY) AS TOTALPRIZEMONEY FROM COMPETITOR C2, COMPETITION C1 WHERE NATIONALITY IN (SELECT COUNTRY FROM COUNTRY_TABLE) AND C1.TIMEPLANNED BETWEEN TO_DATE('01-JAN-15') AND TO_DATE ('31-DEC-15') GROUP BY NATIONALITY; — Roger Dwan
– Roger Dwan, Commented Nov 27, 2015 at 7:19

Eduard Uta · Accepted Answer · 2015-11-27 07:30:04Z

2

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;

answered Nov 27, 2015 at 7:30

Eduard Uta

2,6165 gold badges30 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HAYMbl4 Over a year ago

In this varian query should use join instead in:

marmarta Over a year ago

There is actually no point in using GROUP BY in the subquery (the (SELECT COUNTRY FROM COMPETITION GROUP BY COUNTRY) bit) - in my experience, most of the time eliminating duplicates in subquery like this either does nothing or decreases performance.

Jagdish Patil · Accepted Answer · 2015-11-27 07:25:05Z

2

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.

answered Nov 27, 2015 at 7:25

Jagdish Patil

211 bronze badge

Comments

Avrajit Roy · Accepted Answer · 2015-11-27 07:32:15Z

1

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;

answered Nov 27, 2015 at 7:32

Avrajit Roy

3,3131 gold badge16 silver badges25 bronze badges

Comments

HAYMbl4 · Accepted Answer · 2015-11-27 07:33:26Z

1

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;

answered Nov 27, 2015 at 7:33

HAYMbl4

1,4802 gold badges15 silver badges29 bronze badges

Collectives™ on Stack Overflow

Oracle Database SQL Query

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related