Page 1 of 1

[SOLVED] How Do I Make A Summary Query On One Field?

PostPosted: Mon Jul 09, 2018 9:30 pm
by wagtunes
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.

Re: How Do I Make A Summary Query On One Field?

PostPosted: Mon Jul 09, 2018 10:28 pm
by FJCC
Not having a table with which to test, this probably has a mistake in it.
Code: Select all   Expand viewCollapse view
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"

Re: How Do I Make A Summary Query On One Field?

PostPosted: Mon Jul 09, 2018 10:51 pm
by wagtunes
FJCC wrote:Not having a table with which to test, this probably has a mistake in it.
Code: Select all   Expand viewCollapse view
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"


Well, like you said, doesn't work. Syntax Error.

Anybody else?

Re: How Do I Make A Summary Query On One Field?

PostPosted: Mon Jul 09, 2018 11:25 pm
by FJCC
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   Expand viewCollapse view
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"

Re: How Do I Make A Summary Query On One Field?

PostPosted: Tue Jul 10, 2018 12:34 am
by wagtunes
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   Expand viewCollapse view
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"


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?

Re: How Do I Make A Summary Query On One Field?

PostPosted: Tue Jul 10, 2018 1:12 am
by FJCC
Use
Code: Select all   Expand viewCollapse view
ORDER BY Album_Avg DESC

at the end

Re: How Do I Make A Summary Query On One Field?

PostPosted: Tue Jul 10, 2018 1:41 am
by wagtunes
FJCC wrote:Use
Code: Select all   Expand viewCollapse view
ORDER BY Album_Avg DESC

at the end


Thanks, that did it.

Marking this solved and closing