Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

I am having trouble figuring out how to order the following SQL query. When I add "order by H.Agency, H.ActivityType" at the end of the query I get a syntax error near keyword order. Please help!!

```
With CTE_Hours as (select distinct H.AgencyID, H.Agency,H.Hours, isnull(H.Classification,'') Classification, isnull(H.Objectives,'') Objectives,H.ActivityType,H.ActivityOther, H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther, H.ActivityID, H.RegID , R.AgeCurrent, R.CommunityCommittee, R.YouthCommittee, R.Parentcheck, R.CommunityResident, R.Race, R.Gender, R.Sector , cast(H.ActivityDate as Date) ActivityDate, H.Fiscal from tblOrgHours H inner join tblOrgRegistrations R on H.Regid = R.RegID and R.AgeCurrent between 0 and 99 Where
(H.Agency = 'Administrator' OR H.Agency = 'Chicago Area Project -') And H.Fiscal = 2019 And H.ActivityDate >= '07/01/2018' And H.ActivityDate < '12/31/2018' And R.RegDate >= '07/01/2018' And R.RegDate < '12/31/2018') select H.Agency ,A.ActivityName ,H.Classification ,H.ActivityDate ,H.Objectives ,H.Hours ,count(A.ActivityName) over (partition by H.Agency,A.ActivityName order by A.activityName) as [ActivityCount] ,Count(H.RegID) as [# individuals] ,H.[ActivityType],H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther ,COUNT(CASE when R.CommunityCommittee = '1' then 1 end) as [CommunityCommittee]
,COUNT(CASE when R.YouthCommittee = '1' then 1 end) as [YouthCommittee] ,COUNT(CASE when R.Parentcheck = '1' then 1 end) as [Parentcheck] ,COUNT(CASE when R.CommunityResident = '1' then 1 end) as [CommunityResident] ,COUNT(CASE when R.Sector = 'Business' then 1 end) as [Business] ,COUNT(CASE when R.Sector = 'Civic-Volunteer' then 1 end) as [Civic-Volunteer] ,COUNT(CASE when R.Sector = 'Community Resident' then 1 end) as [Community Resident] ,COUNT(CASE when R.Sector = 'Faith Based' then 1 end) as [Faith Based] ,COUNT(CASE when R.Sector = 'Healthcare' then 1 end) as [Healthcare]
,COUNT(CASE when R.Sector = 'Human Support Agencies' then 1 end) as [Human Support Agencies] ,COUNT(CASE when R.Sector = 'Law Enforcement' then 1 end) as [Law Enforcement] ,COUNT(CASE when R.Sector = 'Local Government' then 1 end) as [Local Government] ,COUNT(CASE when R.Sector = 'Media' then 1 end) as [Media] ,COUNT(CASE when R.Sector = 'Parent or Guardian' then 1 end) as [Parent or Guardian] ,COUNT(CASE when R.Sector = 'Philanthropic' then 1 end) as [Philanthropic] ,COUNT(CASE when R.Sector = 'Schools' then 1 end) as [Schools] ,COUNT(CASE when R.Sector = 'Youth' then 1 end) as [Youth]
from CTE_Hours H inner join tblOrgRegistrations R on H.Regid = R.RegID inner join tblOrgActivities A on H.ActivityID = A.ActivityID group by H.Agency,H.Classification,H.Hours, A.ActivityName,H.ActivityDate, H.Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther Order by H.Agency, H.Classification,A.ActivityName, H.ActivityDate order by H.ActivityType
```

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

For example, here is how I noticed it. I formatted the SQL and indented. Once I got the to last order by, I noticed the extra one.

```
With CTE_Hours as (
select distinct H.AgencyID, H.Agency,H.Hours, isnull(H.Classification,'') Classification, isnull(H.Objectives,'') Objectives,
H.ActivityType,H.ActivityOther, H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther, H.ActivityID, H.RegID ,
R.AgeCurrent, R.CommunityCommittee, R.YouthCommittee, R.Parentcheck, R.CommunityResident, R.Race, R.Gender, R.Sector ,
ast(H.ActivityDate as Date) ActivityDate, H.Fiscal
from tblOrgHours H
inner join tblOrgRegistrations R on H.Regid = R.RegID and R.AgeCurrent between 0 and 99
Where (H.Agency = 'Administrator' OR H.Agency = 'Chicago Area Project -') And H.Fiscal = 2019 And
H.ActivityDate >= '07/01/2018' And H.ActivityDate < '12/31/2018' And R.RegDate >= '07/01/2018' And R.RegDate < '12/31/2018'
)
select H.Agency ,A.ActivityName ,H.Classification ,H.ActivityDate ,H.Objectives ,H.Hours ,
count(A.ActivityName) over (partition by H.Agency,A.ActivityName order by A.activityName) as [ActivityCount] ,
Count(H.RegID) as [# individuals] ,H.[ActivityType],H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther ,
COUNT(CASE when R.CommunityCommittee = '1' then 1 end) as [CommunityCommittee],
COUNT(CASE when R.YouthCommittee = '1' then 1 end) as [YouthCommittee] ,
COUNT(CASE when R.Parentcheck = '1' then 1 end) as [Parentcheck] ,
COUNT(CASE when R.CommunityResident = '1' then 1 end) as [CommunityResident] ,
COUNT(CASE when R.Sector = 'Business' then 1 end) as [Business] ,
COUNT(CASE when R.Sector = 'Civic-Volunteer' then 1 end) as [Civic-Volunteer] ,
COUNT(CASE when R.Sector = 'Community Resident' then 1 end) as [Community Resident] ,
COUNT(CASE when R.Sector = 'Faith Based' then 1 end) as [Faith Based] ,
COUNT(CASE when R.Sector = 'Healthcare' then 1 end) as [Healthcare],
COUNT(CASE when R.Sector = 'Human Support Agencies' then 1 end) as [Human Support Agencies] ,
COUNT(CASE when R.Sector = 'Law Enforcement' then 1 end) as [Law Enforcement] ,
COUNT(CASE when R.Sector = 'Local Government' then 1 end) as [Local Government] ,
COUNT(CASE when R.Sector = 'Media' then 1 end) as [Media] ,
COUNT(CASE when R.Sector = 'Parent or Guardian' then 1 end) as [Parent or Guardian] ,
COUNT(CASE when R.Sector = 'Philanthropic' then 1 end) as [Philanthropic] ,
COUNT(CASE when R.Sector = 'Schools' then 1 end) as [Schools] ,
COUNT(CASE when R.Sector = 'Youth' then 1 end) as [Youth]
from CTE_Hours H
inner join tblOrgRegistrations R on H.Regid = R.RegID
inner join tblOrgActivities A on H.ActivityID = A.ActivityID
group by H.Agency,H.Classification,H.Hours, A.ActivityName,H.ActivityDate, H.Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,
H.Frequency,H.Strategy,H.Need,H.NeedOther
Order by H.Agency, H.Classification,A.ActivityName, H.ActivityDate
order by H.ActivityType
```

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial```
With CTE_Hours as (select distinct H.AgencyID, H.Agency,H.Hours, isnull(H.Classification,'') Classification, isnull(H.Objectives,'') Objectives,H.ActivityType,H.ActivityOther, H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther, H.ActivityID, H.RegID , R.AgeCurrent, R.CommunityCommittee, R.YouthCommittee, R.Parentcheck, R.CommunityResident, R.Race, R.Gender, R.Sector , cast(H.ActivityDate as Date) ActivityDate, H.Fiscal from tblOrgHours H inner join tblOrgRegistrations R on H.Regid = R.RegID and R.AgeCurrent between 0 and 99 Where
(H.Agency = 'Administrator' OR H.Agency = 'Chicago Area Project -') And H.Fiscal = 2019 And H.ActivityDate >= '07/01/2018' And H.ActivityDate < '12/31/2018' And R.RegDate >= '07/01/2018' And R.RegDate < '12/31/2018') select H.Agency ,A.ActivityName ,H.Classification ,H.ActivityDate ,H.Objectives ,H.Hours ,count(A.ActivityName) over (partition by H.Agency,A.ActivityName order by A.activityName) as [ActivityCount] ,Count(H.RegID) as [# individuals] ,H.[ActivityType],H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther ,COUNT(CASE when R.CommunityCommittee = '1' then 1 end) as [CommunityCommittee]
,COUNT(CASE when R.YouthCommittee = '1' then 1 end) as [YouthCommittee] ,COUNT(CASE when R.Parentcheck = '1' then 1 end) as [Parentcheck] ,COUNT(CASE when R.CommunityResident = '1' then 1 end) as [CommunityResident] ,COUNT(CASE when R.Sector = 'Business' then 1 end) as [Business] ,COUNT(CASE when R.Sector = 'Civic-Volunteer' then 1 end) as [Civic-Volunteer] ,COUNT(CASE when R.Sector = 'Community Resident' then 1 end) as [Community Resident] ,COUNT(CASE when R.Sector = 'Faith Based' then 1 end) as [Faith Based] ,COUNT(CASE when R.Sector = 'Healthcare' then 1 end) as [Healthcare]
,COUNT(CASE when R.Sector = 'Human Support Agencies' then 1 end) as [Human Support Agencies] ,COUNT(CASE when R.Sector = 'Law Enforcement' then 1 end) as [Law Enforcement] ,COUNT(CASE when R.Sector = 'Local Government' then 1 end) as [Local Government] ,COUNT(CASE when R.Sector = 'Media' then 1 end) as [Media] ,COUNT(CASE when R.Sector = 'Parent or Guardian' then 1 end) as [Parent or Guardian] ,COUNT(CASE when R.Sector = 'Philanthropic' then 1 end) as [Philanthropic] ,COUNT(CASE when R.Sector = 'Schools' then 1 end) as [Schools] ,COUNT(CASE when R.Sector = 'Youth' then 1 end) as [Youth]
from CTE_Hours H inner join tblOrgRegistrations R on H.Regid = R.RegID inner join tblOrgActivities A on H.ActivityID = A.ActivityID group by H.Agency,H.Classification,H.Hours, A.ActivityName,H.ActivityDate, H.Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther
Order by H.Agency, H.Classification,A.ActivityName, H.ActivityDate,H.ActivityType
```

Order by H.Agency, H.Classification,A.Activit

Query Syntax

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

At the end of that you have:

Order by H.Agency, H.Classification,A.Activit

order byH.ActivityType