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

Creating tables and queries

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

Postby wagtunes » Mon Jul 09, 2018 9:30 pm

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.
Last edited by robleyd on Tue Jul 10, 2018 3:32 am, edited 2 times in total.
Reason: Add green tick [robleyd, Moderator]
OpenOffice 3.4.1 on Windows 8
wagtunes
 
Posts: 17
Joined: Tue Apr 26, 2016 6:11 pm

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

Postby FJCC » Mon Jul 09, 2018 10:28 pm

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"
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7204
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby wagtunes » Mon Jul 09, 2018 10:51 pm

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?
OpenOffice 3.4.1 on Windows 8
wagtunes
 
Posts: 17
Joined: Tue Apr 26, 2016 6:11 pm

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

Postby FJCC » Mon Jul 09, 2018 11:25 pm

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"
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7204
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby wagtunes » Tue Jul 10, 2018 12:34 am

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?
OpenOffice 3.4.1 on Windows 8
wagtunes
 
Posts: 17
Joined: Tue Apr 26, 2016 6:11 pm

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

Postby FJCC » Tue Jul 10, 2018 1:12 am

Use
Code: Select all   Expand viewCollapse view
ORDER BY Album_Avg DESC

at the end
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7204
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby wagtunes » Tue Jul 10, 2018 1:41 am

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
OpenOffice 3.4.1 on Windows 8
wagtunes
 
Posts: 17
Joined: Tue Apr 26, 2016 6:11 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests