How do I make a total amount from multiple values sharing a similar ID in the database?

Question

0.00/5 (No votes)

See more:

Hi,

I want to select 3 columns from 3 different tables that are joined together.

I have an albums table that has albumid, genreid and name, and a genre table has genreid and name and Ive joined them together as follows:

SQL

SELECT a.albumid, a.name, g.name as genrename
FROM albums a
JOIN genre g
ON a.genreid=g.genreid
ORDER BY a.name;

However, I have another table called tracks that contains the tracks linked with their respective album (linked by albumid which is present in both tables). in the tracks table each track has a duration for the song.

What I am trying to achieve is, with the SQL I already have, I would like to get a sum of all the track durations added together to get a total for that album, which can then be displayed as album duration.

Something like this, but with some kind of subquery to calculate album duration:

SQL

SELECT a.albumid, a.name, g.name as genrename
FROM albums a
JOIN genre g
ON a.genreid=g.genreid
JOIN tracks t
ON a.albumid=t.albumid
ORDER BY a.name;

Posted 28-Apr-15 9:51am

jba1991

Updated 28-Apr-15 9:53am

v2

Add a Solution

Comments

Sascha Lefèvre 28-Apr-15 16:04pm

Oh hey, just realized it's you - no PHP today ;-)

jba1991 29-Apr-15 8:34am

Haha no not today :P

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sascha Lefèvre · Accepted Answer · 2015-04-28T09:59:00

Solution 1

This should work, you would just have to adjust the column name tracks.duration if it's different:

SQL

SELECT a.albumid, a.name, g.name as genrename, sum(t.duration) as albumduration
FROM albums a
JOIN genre g
ON a.genreid=g.genreid
JOIN tracks t
ON a.albumid=t.albumid
GROUP BY a.albumid, a.name, g.name
ORDER BY a.name;

Posted 28-Apr-15 9:59am

Sascha Lefèvre

Comments

jba1991 29-Apr-15 8:34am

Correct as always. Thanks :)

Sascha Lefèvre 29-Apr-15 8:43am

You're welcome! :)

jba1991 29-Apr-15 13:41pm

How could I convert sum(t.duration) into HH:MM:SS from seconds inside SQL? is that possible?

Sascha Lefèvre 29-Apr-15 13:42pm

In MySQL yes?

jba1991 29-Apr-15 13:48pm

well SQLite

Sascha Lefèvre 29-Apr-15 13:56pm

Please see here:

http://www.sqlite.org/lang_datefunc.html
http://stackoverflow.com/questions/5287064/format-integer-to-formatted-date-in-an-sqlite-select-statement
http://stackoverflow.com/questions/2685956/how-would-you-convert-secs-to-hhmmss-format-in-sqlite
http://sqlfiddle.com/#!5/b3b70/3

jba1991 29-Apr-15 14:10pm

SELECT strftime('%H-%i-%s', sum(t.duration), 'unixepoch') ??

Sascha Lefèvre 29-Apr-15 14:18pm

Yes, except for the format string: http://sqlfiddle.com/#!5/b3b70/5

jba1991 29-Apr-15 14:21pm

okay and how would i implement this into the sql i already have? do i wrap it in parenthesis?

Sascha Lefèvre 29-Apr-15 14:24pm

just put in in place of sum(t.duration)

jba1991 29-Apr-15 14:25pm

okay cool. It saying theres a syntax error using :

is there a substitute or can i comment them out or something?

Sascha Lefèvre 29-Apr-15 14:27pm

pardon? :)

jba1991 29-Apr-15 14:29pm

syntax error, unexpected ':'

Sascha Lefèvre 29-Apr-15 14:29pm

please show your query

jba1991 29-Apr-15 14:31pm

SELECT a.albumid as albumid, a.name as name, a.year as year,
g.name as genrename, strftime("%H:%M:%S", sum(t.totaltime), 'unixepoch') as albumduration
FROM album a
JOIN genre g
ON a.genreid=g.genreid
JOIN track t
ON a.albumid=t.albumid
GROUP BY a.albumid, a.name, g.name
ORDER BY a.name

Sascha Lefèvre 29-Apr-15 14:37pm

Hm.. that's how I think it should look like. Maybe use the time(..) function instead (see SQLFiddle)

jba1991 29-Apr-15 14:45pm

that worked :) thanks

Sascha Lefèvre 29-Apr-15 14:45pm

Cheers :)

jba1991 29-Apr-15 14:49pm

i think t.duration is in milliseconds not seconds

Sascha Lefèvre 29-Apr-15 14:53pm

divide the sum by 1000 :)

jba1991 29-Apr-15 14:53pm

haha nice, that was just simple maths and common sense! I need a coffee! ha thanks

jba1991 29-Apr-15 14:47pm

actually, how is time() formatting the result? it doesnt seem correct

Sascha Lefèvre 29-Apr-15 14:53pm

how?