Help with a column total

Question

Post reply

Help with a column total

Opus

Default port

Points: 1419
More actions
May 2, 2007 at 10:56 am

#173887

I'm trying to get one row per grade level (graden) with the total on credits (GradCrAwd). I know I missing something fairly simple.
------------------------
SELECT
RTRIM(studemo.lastname)+', '+RTRIM(studemo.firstname)+' '+RTRIM(studemo.middlename) AS student,
Trnscrpt.SchYear,
Trnscrpt.seschoolc,
Trnscrpt.GradeN,
Trnscrpt.GradCrAwd
FROM Trnscrpt LEFT JOIN CrsHist ON CrsHist.CrsHisuniq = Trnscrpt.CrsHisuniq INNER JOIN
studemo ON Trnscrpt.suniq = studemo.suniq INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark
WHERE Trnscrpt.Suniq = 320838 AND
Trnscrpt.GradeN >= 9 AND
Trnscrpt.GradCrAwd IS NOT NULL AND
markawd1 IS NOT NULL AND
markawd1 <> '' AND
(gpatypes LIKE '%O%' OR gpatypes = '' OR gpatypes IS NULL)
ORDER BY
Trnscrpt.GradeN desc
--------------------------------------
student SchYear seschoolc GradeN GradCrAwd
Doe, Jane 2007 33725 10 0.2500
Doe, Jane 2007 33725 10 0.2500
Doe, Jane 2007 33725 10 0.2500
Doe, Jane 2007 33725 10 0.2500
Doe, Jane 2007 33725 10 0.2500
Doe, Jane 2007 33725 10 0.2500
...........
Doe, Jane 2007 33725 9 0.2500
Doe, Jane 2007 33725 9 0.2500
Doe, Jane 2007 33725 9 0.2500
Doe, Jane 2007 33725 9 0.2500
Doe, Jane 2007 33725 9 0.2500
Doe, Jane 2007 33725 9 0.2500

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Looks like you are running Zangle. I will look at your query a bit and see what I can do.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

I simplified your query some and ran it against a copy of ZANGLE database. Take it and make whatever changes you need, but I think this is what you are looking for:

select

rtrim(studemo.lastname) + ', ' + rtrim(studemo.firstname) + ' ' + rtrim(studemo.middlename) as student,

trnscrpt.suniq,

trnscrpt.schyear,

trnscrpt.seschoolc,

trnscrpt.graden,

sum(trnscrpt.gradcrawd) as gradcrawd

from

dbo.trnscrpt

inner join dbo.studemo

on (trnscrpt.suniq = studemo.suniq)

where

trnscrpt.suniq = 1097180

and trnscrpt.graden >= 9

group by

rtrim(studemo.lastname) + ', ' + rtrim(studemo.firstname) + ' ' + rtrim(studemo.middlename),

trnscrpt.suniq,

trnscrpt.schyear,

trnscrpt.seschoolc,

trnscrpt.graden

order by

trnscrpt.suniq

Opus Default port Points: 1419 More actions · Answer 3

danke schön!!! Now I have to google ZANGLE to find out what it is. 🙂

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Lynn Pettis

SSC Guru

Points: 442467

May 2, 2007 at 12:26 pm

#703904

ZANGLE is a K-12 Student Information System.