• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Min/Max/Sum

Explorer ,
Feb 28, 2008 Feb 28, 2008

Copy link to clipboard

Copied

I’m trying to work out how long it takes a user to complete online training.

The following query returns 271 rows form 4 different training courses, i want to take the maximum & minimum for each course and work out the difference.

How can I achieve this?

select pps_principals.name as UserName, pps_transcripts.status, pps_transcripts.score, pps_transcripts.max_score as MaxScore, pps_scos.name as ModuleName, pps_transcript_details.date_created as DateCreated
from pps_principals, pps_transcripts, pps_scos, pps_transcript_details
where pps_principals.principal_id = pps_transcripts.principal_id
and pps_transcripts.sco_id = pps_scos.sco_id
and pps_transcripts.transcript_id = pps_transcript_details.transcript_id
and pps_principals.name = 'Lee Duncan'
and pps_transcripts.status = 'F'
and pps_scos.name like 'MTM-%'
and pps_transcripts.ticket not like 'l-%'

Thanks
Lee
TOPICS
Advanced techniques

Views

838

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 28, 2008 Feb 28, 2008

Copy link to clipboard

Copied

you want a max and min of exactly which columns?

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 28, 2008 Feb 28, 2008

Copy link to clipboard

Copied

pps_scos.name, that query returns four different training courses and the min and max for pps_transcript_details.date_created

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 28, 2008 Feb 28, 2008

Copy link to clipboard

Copied

hmm... confused... so MAX and MIN of WHAT do you want from that query?
as far as i can see, pps_scos.name is just a varchar field holding some
sort of name of a course or something...

which of the fields returned by your query will have the values you want
to get MAX and MIN of?

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 28, 2008 Feb 28, 2008

Copy link to clipboard

Copied

Ok, let me try explain myself better and explain the situation.

Every time someone takes an online training course, made using PowerPoint, a date created field is input into the database, this is done every time they view a new slide.

I want to be able to select a user, return all the courses they have failed and the time it took. I can’t find a start and end date, so the only way I can see of working this out is by taking the min and max date created (pps_transcript_details.date_created) for a distinct training course (pps_scos.name) and working out the difference.

The above query returns all the data for all the course I have failed (4), but it returns 271 rows because it also returns all the interactions (slide views), I want to narrow it down to the first & last entry, min and max, but for each individual course.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 28, 2008 Feb 28, 2008

Copy link to clipboard

Copied

if you need just MIN and MAX value of
pps_transcript_details.date_created for each distinct course, you can do
something like:

SELECT ps.name as ModuleName, MIN(ptd.date_created) as MinDateCreated,
MAX(ptd.date_created) as MaxDateCreated
FROM pps_principals pp, pps_scos ps, pps_transcripts pt,
pps_transcript_details ptd
WHERE 1=1
AND pp.principal_id = pt.principal_id
AND pt.sco_id = ps.sco_id
AND pt.transcript_id = ptd.transcript_id
AND pp.name = 'Lee Duncan'
AND pt.status = 'F'
AND ps.name like 'MTM-%'
AND pt.ticket not like 'l-%'
GROUP BY ps.name

NOTE: the above is untested, but should work... :)


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 28, 2008 Feb 28, 2008

Copy link to clipboard

Copied

Date functions are db specific, but here is the general idea.

select min(datediff(minute, Enddate, startdate)) min_duration,
max(datediff(minute, Enddate, startdate)) max_duration,
avg(datediff(minute, Enddate, startdate)) avg_duration,

from some_table join
(select theuser, min(datefield) startdate, max(datefield) enddate

from sometables
where they failed
group by theuser ) x on x.theuser = some_table.the_user

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 28, 2008 Feb 28, 2008

Copy link to clipboard

Copied

LATEST
much appreciated!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation