How to make query with multiple count?

[yosiro]

There are 3 tables:

scoreT
-----------------------
id_score | id_name | score
1 | 1 | 350
2 | 2 | 400
3 | 3 | 450
4 | 4 | 330
5 | 5 | 500


nameT
------------------------
id_name | name | id_country
1 | Bill | 1
2 | Ana | 2
3 | Ted | 2
4 | Ryan | 3
5 | Kyle | 3


countryT
id_country | country
1 | USA
2 | INA
3 | GER


So how to make query table like this?

country | count of name | average of Score | count name who have Score >= 400 | percentage of name who have Score >= 400 |

USA | 1 | 350 | 0 | 0
INA | 2 | 425 | 2 | 100
GER | 2 | 415 | 1 | 50

[Luuk]

ok, next SQL is untested, an syntax might not be valid in MSSQL

SELECT 
    X.country, 
    COUNT(X.id_name) AS count_of_name,
    AVERAGE(X.score) AS averag_of_Score,
    COUNT(X.bigger) AS higher_schore_than_400,
    COUNT(X.bigger)/COUNT(X.id_name) AS percentage
FROM (
    SELECT s.id_score, s.id_name, s.score, n.name, n.id_country, c.country, CASE WHEN s.score>=400 THEN 1 ELSE 0 END AS bigger
    FROM scoreT s
    INNER JOIN nameT n ON n.id_name=s.id_name
    INNER JOIN countryT c ON c.id_country=n.id_country
    GROUP BY country
    ) X
 

[yosiro]

there is a warning "Characters found after end of SQL statement"

[ADezii]

I was able to arrive at a solution but not within the context of a single Query, which was my original intention. I ended up using a Totals Query with two Calculated Fields and a Select Query to accomplish the task. Rather than go into a detailed explanation, I'll simply Post my Demo, and should you have any questions feel free to ask. This is, in my opinion, not the optimal solution, but until a better one comes along it is something to look at. Out of curiosity, this appears to be some form of homework assignment, is it?

P.S. - Pay special attention to the Relationships among the three Tables which make it all work.

[yosiro]

@ADezii

It's work for me now. Thanks