434,921 Members | 1,433 Online
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

# How to make query with multiple count?

 P: 34 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 Nov 26 '16 #1

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.

4 Replies

 Expert 100+ P: 1,035 ok, next SQL is untested, an syntax might not be valid in MSSQL Expand|Select|Wrap|Line Numbers 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   Nov 26 '16 #2

 P: 34 there is a warning "Characters found after end of SQL statement" Nov 28 '16 #3

Expert 5K+
P: 8,638
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.
Attached Files
 How to Make Query.zip (27.9 KB, 34 views)
Nov 28 '16 #4

 P: 34 @ADezii It's work for me now. Thanks Nov 29 '16 #5