CODEWITHSUNDEEP
sql-server-2008stored-procedures
Masriyah
Masriyah

Reputation: 2505

Group By in stored procedure error

I created this stored procedure which is basically to return a list of offices with the type of activities that happen within each office. The results i reported to reportviewer but i noticed that for each activity return it creates a table - so i can have 5 different tables each with its own activity but all happen in the same office. I want the report to be a table for each office which will contain as many activites as there are for each office. So i thought that if i grouped in my stored procedure my results will be as what i want but i am getting column error saying: "...is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

I am not sure how to go about that but here is my select, from, where, group by statements: 

SELECT 
            O.OfficeId,
            O.OfficeName AS Office,
            HT.Description AS HearingType,
            H.HearingDate AS HearingDate,
            CR.Description AS Court,
            CT.[Description]AS CaseType
    FROM Activity H
    INNER JOIN ActivityEntry HE ON H.ActivityEntryId = HE.ActivityEntryId
    INNER JOIN ActivityType HT ON H.ActivityTypeId = HT.ActivityTypeId
    INNER JOIN [Case] C ON H.CaseId = C.CaseId
    INNER JOIN [Office] O ON HE.CreatedByOfficeId = O.OfficeId
    INNER JOIN [User] U ON C.CreatedByUserId = U.UserId
    LEFT OUTER JOIN CaseType CT ON C.CaseTypeId = CT.CaseTypeId
    LEFT OUTER JOIN Court CR ON C.CourtId = CR.CourtId
    WHERE .dbo.DateOnly(HE.HearingDate)BETWEEN @BeginDate AND @EndDate
    GROUP  BY
            O.OfficeId,
            O.OfficeName,
            HT.Description
    ORDER BY O.OfficeId, HT.Description

Upvotes: 1

Views: 1185

Answers (3)

Masriyah
Masriyah

Reputation: 2505

I actually realized that my problem cannot be solved through my stored procedure as mentioned from some of the members. Since i am displaying results in my report so i re-organized my report and dataset information so that there is a parent and child relationship and from the dataset my information was organized properly. I used the solutions offered from this post to help guide me: post used to help guide me.

Upvotes: 0

Kendrick
Kendrick

Reputation: 3787

You aren't using any aggregate functions (on first glance anyway) so you don't need a group by clause. You can do all your ordering in the order by and then extract it into different datasets as you process it on the application side.

Example:

select ... from ... order by OfficeID, Description

This returns a single result for all offices. Now you need to parse it in code

int OfficeID=-1;
while(recordset.moveToNextRow())
{
    if(currentRecord.OfficeID!=OfficeID)
    {
        //This is a new office, do whatever you need to do to split the data up here
        OfficeID=currentRecord.OfficeID;
    }
    //Process the record as a part of the current office here
}

So if you were building a table on a webpage, you'd maybe end the last table and start a new table every time you hit a new office ID. There's some additional logic you'll need here, but this should give you the idea.

Note that your problem has nothing to do with using a stored procedure and everything to do with how you are selecting and processing data.

Upvotes: 1

marc_s
marc_s

Reputation: 754408

GROUP BY requires that you have some kind of an aggregate function in your list of columns - a SUM, an AVG, a COUNT. GROUP BY only makes sense in combination with an aggregate.

Otherwise, just simply order your data with an ORDER BY statement.

Upvotes: 1

Related Questions