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
4 801 Luuk 1,047
Recognized Expert Top Contributor
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
-
there is a warning "Characters found after end of SQL statement"
ADezii 8,834
Recognized Expert Expert
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.
@ADezii
It's work for me now. Thanks
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Alan Little |
last post by:
I have a table with links, a log table to record clicks, and a vote table
to record votes on the links.
Is it possible to get the link data, the click count, vote count and vote
total in one...
|
by: jenkinsloveschicken |
last post by:
I am somewhat new to Access and have been tasked with creating a reporting database for my operation. The problem I am having is that I am needing to use the Count function to calculate total...
|
by: djflow |
last post by:
Hi!
II was wondering if you can help me with SQL query..
Below 7 separated select query works fine(only when they are retrieved separately)
But I want to combined them together and so that i...
|
by: newnewbie |
last post by:
Desperately need help in creating a query to count unique values in a table. I am a Business analyst with limited knowledge of Access….My boss got me ODBC connection to the underlying tables for our...
|
by: Noorain |
last post by:
sir
i want a query which count total student. here 6 record of student. here one student two times entry. my result is total student 5. i can't do this.
my table is:
id stu_name dob
1...
| |
by: kkshansid |
last post by:
i hav a table with a column named result where values are either of the three
pass ,fail,absent
can i get multiple count in same row
like
select count(result=pass) as cp,count(result=fail) as...
|
by: AndyB2 |
last post by:
Writing some VBA to call a make table query. It the make query is pulling data from a MS 2008 R2 SQL database then creating the data locally for processing. It works, but not when I call it out of...
|
by: antjonz |
last post by:
I can create a query to count the total number of items in a table, but I need it to show me how many times each item occurs. For example, during the month of March my query shows me that 97 items...
|
by: bwanty |
last post by:
Hi everybody
How can I get this query:
For each month (use dateofappointment) of 2010, show a count of appointments. Also show the count of diagnosed diseases, and a count of patients seen during...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |