I used to do this in Microsoft Access many years ago but don't own it anymore and even though OO seems to be the same functions, I have forgotten how to do this.
Here is my table
Song Name
Music
Lyrics
Performance
Mix
Commercial Appeal
Album Name
Year
I have a query that adds (Music + Lyrics + Performance + Mix + Commercial Appeal) and then divides by 5 to get an average for each record. That works fine.
What I want is to create a query that will get an average for each album. So I know that the first thing I have to do is add up all the sums for each album and then compute the average.
So let's say I have 5 songs in an album named Album 1 and these are their average scores.
75
82
90
78
86
I want to be able to add up all those average scores and print out the average for the whole album.
I have no idea how I did this in Access (been too long) and have no clue how to do this in OO.
Can somebody give me a hand?
Thanks.
[SOLVED] How Do I Make A Summary Query On One Field?
[SOLVED] How Do I Make A Summary Query On One Field?
Last edited by robleyd on Tue Jul 10, 2018 3:32 am, edited 2 times in total.
Reason: Add green tick [robleyd, Moderator]
Reason: Add green tick [robleyd, Moderator]
OpenOffice 3.4.1 on Windows 8
Re: How Do I Make A Summary Query On One Field?
Not having a table with which to test, this probably has a mistake in it.
Code: Select all
SELECT "Album Name" AVG("Score") AS "Album_Avg"
FROM (SELECT "Song Name", "Album Name", ("Music" + "Lyrics" + "Performance" + "Mix" + "Commercial Appeal")/5 AS "Score"
FROM "MyTable"
Group By "Song Name", "Album Name")
GROUP BY "Album Name"
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: How Do I Make A Summary Query On One Field?
Well, like you said, doesn't work. Syntax Error.FJCC wrote:Not having a table with which to test, this probably has a mistake in it.Code: Select all
SELECT "Album Name" AVG("Score") AS "Album_Avg" FROM (SELECT "Song Name", "Album Name", ("Music" + "Lyrics" + "Performance" + "Mix" + "Commercial Appeal")/5 AS "Score" FROM "MyTable" Group By "Song Name", "Album Name") GROUP BY "Album Name"
Anybody else?
OpenOffice 3.4.1 on Windows 8
Re: How Do I Make A Summary Query On One Field?
There is a missing comma in my first version. I also changed the value of the division to 5.0 so that the result would not be forced to be an integer. If this doesn't work, please upload an example file so we have something to work with.
Code: Select all
SELECT "Album Name", AVG("Score") AS "Album_Avg"
FROM (SELECT "Song Name",
"Album Name",
("Music" + "Lyrics" + "Performance" + "Mix" + "Commercial Appeal")/5.0 AS "Score"
FROM "MyTable"
Group By "Song Name", "Album Name")
GROUP BY "Album Name"
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: How Do I Make A Summary Query On One Field?
Okay, that works. I'd like to add one thing to this, have it sort from high to low. Do I just add SORT by "Album_Avg" after the last GROUP BY line?FJCC wrote:There is a missing comma in my first version. I also changed the value of the division to 5.0 so that the result would not be forced to be an integer. If this doesn't work, please upload an example file so we have something to work with.Code: Select all
SELECT "Album Name", AVG("Score") AS "Album_Avg" FROM (SELECT "Song Name", "Album Name", ("Music" + "Lyrics" + "Performance" + "Mix" + "Commercial Appeal")/5.0 AS "Score" FROM "MyTable" Group By "Song Name", "Album Name") GROUP BY "Album Name"
OpenOffice 3.4.1 on Windows 8
Re: How Do I Make A Summary Query On One Field?
Use
at the end
Code: Select all
ORDER BY Album_Avg DESC
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: How Do I Make A Summary Query On One Field?
Thanks, that did it.FJCC wrote:Useat the endCode: Select all
ORDER BY Album_Avg DESC
Marking this solved and closing
OpenOffice 3.4.1 on Windows 8