Sqlzoo SELECT within SELECT Tutorial #5

Question

My Question is:

Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.

Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

My answer:

SELECT name,CONCAT(ROUND(population/80000000,-2),'%')
FROM world
WHERE population = (SELECT population
                    FROM world
                      WHERE continent='Europe')

What I am doing wrong? Thanks.

DonCallisto · Accepted Answer · 2015-06-19T10:21:18.433

8

The question was incomplete and was taken from here

This is the answer

SELECT 
  name, 
  CONCAT(ROUND((population*100)/(SELECT population 
                                 FROM world WHERE name='Germany'), 0), '%')
FROM world
WHERE population IN (SELECT population
                     FROM world
                     WHERE continent='Europe')

I was wondering about sub-query as from OP'S question it wasn't clear (at least to me). The reason is that "world" table (as the name suggest, I have to admit) contains all world country whereas we're interested only into european one. Moreover, the population of Germany has to be retrieved from DB because it's not extacly 80.000.000; if you use that number you receive back 101% as Germany population.

edited Jun 19 '15 at 10:21

answered Jun 19 '15 at 10:13

DonCallisto

29,419
9
72
100

14

The last part of the SQL, WHERE population ... is redundant. You can just use WHERE continent = "Europe" instead of that blob. – Ka Mok Jul 09 '16 at 22:50
I'm getting the following output for this SQLZOO system error: error – ABHISHEK D Jul 11 '23 at 10:24

score 3 · Answer 2 · edited May 23 '17 at 11:47

When using sql server in SQL Zoo, then don't use CONCAT:

I think SQL Zoo uses a version of SQL Server that doesn't support CONCAT and furthermore it looks like you have to do a CAST. Instead concatenate with the use of '+'. Also see this post.

I figure the script should be something like beneath (though I haven't got it to my desired stated, because of the fact I want to result to look like 3%;0%;4%;etc. instead of 3.000000000000000%;0.000000000000000%;4.000000000000000%;etc.. And I start a new topic for that one here).

SELECT name, CAST(ROUND(population*100/(SELECT population FROM world WHERE name='Germany'), 0) as varchar(20)) +'%' FROM world WHERE population IN (SELECT population FROM world WHERE continent='Europe')

pratik dhamanekar · Answer 3 · 2015-07-23T09:41:24.683

1

    select name, CONCAT(ROUND((population/(select population from world where name = "Germany"))*100),"%") 
    from world
    where continent= "Europe"

edited Jul 23 '15 at 09:41

answered Jul 23 '15 at 07:00

pratik dhamanekar

113
1
2
9

1

Thanks for answering! Code-only answers [are discouraged](http://meta.stackexchange.com/a/148274) on Stack Overflow, because a code dump with no context doesn't explain how or why the solution will work, making it impossible for the original poster (or any future readers) to understand the logic behind it. Please, edit your question and include an explanation of your code so that others can benefit from your answer! – AHiggins Jul 23 '15 at 14:56

score 1 · Answer 4 · edited May 03 '17 at 19:02

1

SELECT name, CONCAT(ROUND(population/(SELECT population FROM world WHERE name = 'Germany')*100,0), '%')
FROM world
WHERE continent = 'Europe'

edited May 03 '17 at 19:02

Floern

33,559
24
104
119

answered May 03 '17 at 18:48

Miller

11
1

score 1 · Answer 5 · answered Aug 16 '22 at 20:40

1

As of this writing (Aug 16th, 2022), the accepted answer is a percentage without trailing 0s.

Using CONCAT and CAST solves this.

SELECT name, CONCAT(CAST(100*ROUND((population / (SELECT population FROM world WHERE name ='Germany')), 2) AS INT), '%')
FROM world
WHERE continent = 'Europe';

answered Aug 16 '22 at 20:40

BoomBoxBoy

1,770
1
5
23

1

Are you using PostgreSQL? For MySQL, `INT` cause an error. I changed `INT` to `DECIMAL`, still no luck passing. – jdhao Sep 19 '22 at 06:21
Using Microsoft SQL – BoomBoxBoy Sep 19 '22 at 20:30

score 0 · Answer 6 · answered Jun 19 '15 at 09:46

0

sub query should be return multiple data so you can use in function like this

  SELECT name,CONCAT(ROUND(population/80000000,-2),'%')
    FROM world
    WHERE population IN (SELECT population
                        FROM world
                          WHERE continent='Europe')

answered Jun 19 '15 at 09:46

Mukesh Kalgude

4,814
2
17
32

If you want percentage then you will be change division 80000000 to 800 – Mukesh Kalgude Jun 19 '15 at 10:06
You can edit question and add example of data and accepted result – Mukesh Kalgude Jun 19 '15 at 10:10

score 0 · Answer 7 · edited Jan 03 '20 at 10:59

0

Below added code snippet

select name, concat (round(population/(select population from world where 
name='germany')*100,0), '%') from world where continent='Europe'

edited Jan 03 '20 at 10:59

Dulaj Kulathunga

1,248
2
9
19

answered Jan 03 '20 at 09:03

Anshuman Sharma

1

score 0 · Answer 8 · answered Feb 03 '23 at 17:13

0

The following worked for me:


SELECT name, concat(format(ROUND(population / (SELECT population FROM world WHERE name = 'Germany') * 100, 5), '0'), '%') AS percentage
FROM world
WHERE continent = 'Europe';

answered Feb 03 '23 at 17:13

Mark Halstead

76
2

Sqlzoo SELECT within SELECT Tutorial #5

8 Answers8