Vitor Neto Vitor Neto - 1 month ago 6
SQL Question

SUM and ORDER BY - I havent any more hair left

Hi every one i have this table

Pupil ID Fname Lname Form House Week Nr Data
104 fname1 lname1 Year 5W Junior Frobisher 3 5
106 fname2 lname2 Year 4W Junior Grenville 2 5
106 fname2 lname2 Year 4W Junior Grenville 3 4
106 fname2 lname2 Year 4W Junior Grenville 4 3
106 fname2 lname2 Year 4W Junior Grenville 5 5
107 fname3 lname3 Year 5W Junior Grenville 1 1
107 fname3 lname3 Year 5W Junior Grenville 2 3
107 fname3 lname3 Year 5W Junior Grenville 3 5
107 fname3 lname3 Year 5W Junior Grenville 4 1

SELECT PPD.PupilID, PPD.Forename, PPD.Surname, FL.[Description] AS 'Form', HL.[Description] AS 'House', CAST(REPLACE(CM.ColumnTitle, 'Week ', '') AS INT) AS 'WeekNo',CAST(MSDN.Data AS INT) AS 'Data', SUM(CAST(MSDN.Data AS INT)) AS 'Total'
FROM CurrentPupil
INNER JOIN PupilPersonalDetails AS PPD ON PPD.PupilID = CurrentPupil.PupilID
INNER JOIN PupilCurrentSchool AS PCS ON PCS.PupilID = PPD.PupilID
INNER JOIN SchoolLookupDetails AS FL ON PCS.Form = FL.LookupDetailsID AND FL.LookupID = 1002
INNER JOIN SchoolLookupDetails AS HL ON PCS.House = HL.LookupDetailsID AND HL.LookupID = 1001
INNER JOIN MarksheetDataNumeric AS MSDN ON MSDN.PupilID = PPD.PupilID
INNER JOIN ColumnsMaster AS CM ON CM.ColumnID = MSDN.ColumnID AND CM.ColumnTitle LIKE '%week%'
INNER JOIN ClusterMaster AS CLM ON CLM.ClusterID = SUBSTRING(PPD.SchoolID, 0, 4)
INNER JOIN ColumnReportingPeriods AS CRP ON CRP.ColumnID = CM.ColumnID
INNER JOIN ReportingPeriods AS RP ON RP.AcademicYear = CLM.CurrentAcademicYear AND RP.ReportingPeriodID = CRP.ReportingPeriodID
WHERE ('%wc%' = '%wc%')
GROUP BY PPD.PupilID


i am trying to SUM the following column
CAST(MSDN.Data AS INT) AS 'Data'

and add a new one as a total points for each ID (pupil)
CAST(MSDN.Data AS INT) AS 'Data', SUM(CAST(MSDN.Data AS INT)) AS 'Total',
(ithink it is right!!!!)
but the outcome is:
Column PupilPersonalDetails.ForeName is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

help.... sorry i am new at this

Answer

Start from this:

SELECT PPD.PupilID
,PPD.Forename
,PPD.Surname
,SUM(MSDN.Data) 'Total'
FROM CurrentPupil 
INNER JOIN PupilPersonalDetails PPD ON PPD.PupilID=CurrentPupil.PupilID   
INNER JOIN PupilCurrentSchool PCS ON PCS.PupilID=PPD.PupilID 
INNER JOIN SchoolLookupDetails FL ON PCS.Form=FL.LookupDetailsID AND FL.LookupID=1002 
INNER JOIN SchoolLookupDetails HL ON PCS.House=HL.LookupDetailsID AND HL.LookupID=1001 
INNER JOIN MarksheetDataNumeric MSDN ON MSDN.PupilID=PPD.PupilID 
INNER JOIN ColumnsMaster CM ON CM.ColumnID=MSDN.ColumnID AND CM.ColumnTitle LIKE '%week%' 
INNER JOIN ClusterMaster CLM ON CLM.ClusterID=SUBSTRING(PPD.SchoolID,0,4) 
INNER JOIN ColumnReportingPeriods CRP ON CRP.ColumnID=CM.ColumnID
INNER JOIN ReportingPeriods RP ON RP.AcademicYear=CLM.CurrentAcademicYear AND RP.ReportingPeriodID=CRP.ReportingPeriodID
WHERE ('%wc%'='%wc%')
GROUP BY PPD.PupilID
,PPD.Forename
,PPD.Surname

If you want to have more columns in the result you can add them, but since you are using an aggregation function SUM you have to have all these columns - except the one you are aggregating (summing in this case) - mentioned in the GROUP BY clause.

Comments