September 29, 2016 at 10:43 am
Hi there i am trying to output just 2 columns WITH THE RESULTS
COLUMN A COLUMN B
HOUSE NAME TOTALS
HOUSE A TOTAL
HOUSE B TOTAL
HOUSE C TOTAL
HOUSE D TOTAL
At the moment the code below brings out:
HOUSE A - 20
HOUSE A - 45
HOUSE A - 23
HOUSE A - 34
HOUSE A - 2
HOUSE B - 28
HOUSE B - 28
HOUSE B - 28
HOUSE B - 56
HOUSE B - 45
HOUSE C - 22
HOUSE D - 78
HOUSE D - 77
HOUSE D - 11
SELECT HL.[Description] AS 'House', CAST(MSDN.Data AS INT) AS 'Data'
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%')
---------------------------------------------------------------------
what i would like to retrieve only one type from HL.[Description] AS 'House' and sum the column CAST(MSDN.Data AS INT) AS 'Data'
i tried to use DISTINCT but gives me an error
I appreciate any help
vitor
September 29, 2016 at 11:38 am
Please read the link in my signature on how to format and post SQL questions. It will help get better and faster answers.
So, without any idea about the data or why so many joins are needed to get 2 columns, I would guess that you are going to need to SUM and a GROUP BY to get what you want.
SELECT HL.[Description] AS 'House', SUM(CAST(MSDN.Data AS int)) AS 'Data'
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) --<<=== 0 based string?
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 HL.[Description]
I would also suggest you get a book or get online and look for SQL Basics and Fundamentals. Summing and grouping are SQL 101 concepts.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 8, 2016 at 12:12 pm
Thank you so much for the help. really appreciate it.
Excellent.
October 8, 2016 at 3:37 pm
i have one more question....
i have this select
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' 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%') ORDER BY CAST(PPD.PupilID AS INT)
the above will return this
Pupil IDFname Lname Form House Week Nr Points
104 fname1 lname1 Year 5W Junior Frobisher 3 5
106 fname2lname2 Year 4W Junior Grenville 2 5
106 fname2lname2 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 f name3 lname3 Year 5W Junior Grenville 4 1
107 fname3 lname3 Year 5W Junior Grenville 5 8
108 fname4 lname4 Year 5J Junior Raleigh 1 4
108 fname4 lname4 Year 5J Junior Raleigh 2 4
108 fname4lname4 Year 5J Junior Raleigh 3 8
108 fname4 lname4 Year 5J Junior Raleigh 4 13
what i am trying to achieve:
total of points for each pupil (id)
thank you so much for all the help
October 8, 2016 at 9:38 pm
vfn (10/8/2016)
i have one more question....i have this select . . .
the above will return this
Pupil IDFname Lname Form House Week Nr Points
104 fname1 lname1 Year 5W Junior Frobisher 3 5
106 fname2lname2 Year 4W Junior Grenville 2 5
106 fname2lname2 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 f name3 lname3 Year 5W Junior Grenville 4 1
107 fname3 lname3 Year 5W Junior Grenville 5 8
108 fname4 lname4 Year 5J Junior Raleigh 1 4
108 fname4 lname4 Year 5J Junior Raleigh 2 4
108 fname4lname4 Year 5J Junior Raleigh 3 8
108 fname4 lname4 Year 5J Junior Raleigh 4 13
total of points for each pupil (id)
This is basically the same thing as your previous question. I have given you the tools, SUM and GROUP BY. Try applying them to this query and see what you come up with.
Try to come up with output that looks like:
Pupil IDPoints
104 5
106 17
107 18
108 29
If you get this far, you can then build on that query to add the first & last name and other individual data.
And please read the link in my signature line to learn how to post ddl, data and expected output.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 9, 2016 at 5:24 am
HI
I did follow what you taught me using this code
SELECT PPD.PupilID, PPD.Forename, PPD.Surname, FL.[Description] AS 'Form', HL.[Description] AS 'House', CAST(REPLACE(CM.ColumnTitle, 'Week ', '') AS INT) AS 'WeekNo', SUM(CAST(MSDN.Data AS INT)) AS 'Data' 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
but when i try to run it i get
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'PupilPersonalDetails.Surname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
i have tried to put the asking columns in but keeps asking me for the next columns. Have i miss a set of brackets??
October 9, 2016 at 11:42 am
HI There
I have being through this and shortened the sql but still get the same error.
SELECT PPD.PupilID, PPD.Forename, PPD.Surname, 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 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
error:
Column PupilPersonalDetails.Forename is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY Clause
helllpppp
October 9, 2016 at 6:57 pm
vfn (10/9/2016)
error:Column PupilPersonalDetails.Forename is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY Clause
helllpppp
That's right.
If you want to display Forename in the recordset you need to group by it.
Same about Surname, CAST(REPLACE(CM.ColumnTitle, 'Week ', '') AS INT) , CAST(MSDN.Data AS INT).
But I'm not sure you really want to show every unique value in CAST(MSDN.Data AS INT).
It probably should be removed from the SELECT part of the query.
It's also not so clear why do you need to join so many tables when you use data from only 4 of them.
If the rest of tables are here to limit the number of aggregated records then it would be better to put them into WHERE EXISTS subquery.
_____________
Code for TallyGenerator
October 10, 2016 at 9:00 am
Sergiy (10/9/2016)
vfn (10/9/2016)
error:Column PupilPersonalDetails.Forename is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY Clause
helllpppp
That's right.
If you want to display Forename in the recordset you need to group by it.
There is a second option. You could include it in an aggregate. MIN and MAX work with strings.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 10, 2016 at 10:42 am
vfn (10/9/2016)
HII did follow what you taught me using this code . . .
but when i try to run it i get
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'PupilPersonalDetails.Surname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
i have tried to put the asking columns in but keeps asking me for the next columns. Have i miss a set of brackets??
You have not read about SUM and GROUP BY. You need to understand how these 2 work together in order to write a proper query. Putting MIN & MAX around strings to get the desired output is not, in my opinion, a good approach or technique to use. (Though I have done it myself in a pinch!)
First of all, you need some data to work with. Since this is your first time here, I will show you how to do that. But for future posts, please read the link in my signature.
declare @t table
(
PupilID int,
FName varchar(20),
LName varchar(20),
Form char(2),
SchoolYr varchar(10),
House varchar(20),
WeekNr int,
Points int
);
Insert @t (PupilID, FName, LName, Form , SchoolYr, House, WeekNr, Points) values
(104, 'fname1', 'lname1', '5W', 'Junior', 'Frobisher', 3, 5),
(106, 'fname2', 'lname2', '4W', 'Junior', 'Grenville', 2, 5),
(106, 'fname2', 'lname2', '4W', 'Junior', 'Grenville', 3, 4),
(106, 'fname2', 'lname2', '4W', 'Junior', 'Grenville', 4, 3),
(106, 'fname2', 'lname2', '4W', 'Junior', 'Grenville', 5, 5),
(107, 'fname3', 'lname3', '5W', 'Junior', 'Grenville', 1, 1),
(107, 'fname3', 'lname3', '5W', 'Junior', 'Grenville', 2, 3),
(107, 'fname3', 'lname3', '5W', 'Junior', 'Grenville', 3, 5),
(107, 'fname3', 'lname3', '5W', 'Junior', 'Grenville', 4, 1),
(107, 'fname3', 'lname3', '5W', 'Junior', 'Grenville', 5, 8),
(108, 'fname4', 'lname4', '5J', 'Junior', 'Raleigh', 1, 4),
(108, 'fname4', 'lname4', '5J', 'Junior', 'Raleigh', 2, 4),
(108, 'fname4', 'lname4', '5J', 'Junior', 'Raleigh', 3, 8),
(108, 'fname4', 'lname4', '5J', 'Junior', 'Raleigh', 4, 13);
Now try to execute this code against the data:
select PupilID, SUM(Points) totalPoints
from @t
group by pupilID
What do you get?
Now try adding your additional fields into the query and see what happens and then answer the question, "Why?"
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 10, 2016 at 11:10 am
LinksUp (10/10/2016)
Putting MIN & MAX around strings to get the desired output is not, in my opinion, a good approach or technique to use. (Though I have done it myself in a pinch!)
I think it needs to be evaluated based on the situation and that we don't have enough information to make that determination, which is why I mentioned it in the first place.
It's too easy to generalize that if you get that error, then you just place that field in the group by. Sometimes that's what you want and sometimes it isn't. I've seen too many posts where someone's not getting the correct results, because they threw all of the other fields in the group by (including datetime fields with values to the maximum precision).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 10, 2016 at 3:16 pm
Dear all thank you so much for all your help and patience with me.
I have it working !!!
thank you so much.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply