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

[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.

[FJCC]

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"

[wagtunes]

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"

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

Anybody else?

[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

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"

[wagtunes]

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"

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]

Use

Code: Select all

ORDER BY Album_Avg DESC

at the end

[wagtunes]

Use

Code: Select all

ORDER BY Album_Avg DESC

at the end

Thanks, that did it.

Marking this solved and closing