By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,433 Online
Bytes IT Community
+ Ask a Question
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?

yosiro
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

✓ answered by 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.

Share this Question
Share on Google+
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
  1. SELECT 
  2.     X.country, 
  3.     COUNT(X.id_name) AS count_of_name,
  4.     AVERAGE(X.score) AS averag_of_Score,
  5.     COUNT(X.bigger) AS higher_schore_than_400,
  6.     COUNT(X.bigger)/COUNT(X.id_name) AS percentage
  7. FROM (
  8.     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
  9.     FROM scoreT s
  10.     INNER JOIN nameT n ON n.id_name=s.id_name
  11.     INNER JOIN countryT c ON c.id_country=n.id_country
  12.     GROUP BY country
  13.     ) X
  14.  
Nov 26 '16 #2

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

ADezii
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
File Type: zip How to Make Query.zip (27.9 KB, 34 views)
Nov 28 '16 #4

yosiro
P: 34
@ADezii

It's work for me now. Thanks
Nov 29 '16 #5

Post your reply

Sign in to post your reply or Sign up for a free account.