SQL, how to do Group by with Order by

Hi Experts,

I got error message "Column "MakeMyDeal_com.dbo.TestDrivelocal.TimeOfDay" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause."
I think there is a syntax error between group by and order by. Can you help me on it. Thanks/.

----AppointmentTime Testing----

declare @StartDate date
declare @EndDate date
set @StartDate = '2015-06-1'
set @EndDate ='2015-6-30'

-------Get the maxcreatedon for each dealid--
select t.dealid, max(createdon) as maxcreatedon
into #maxcreatedon
from [MakeMyDeal_com].[dbo].[TestDrivelocal] t
inner join Mmd_feed..dealview d on t.dealid=d.dealid 
inner join Mmd_feed..dart dt on dt.dealerid=d.dealerid
where 
d.dealstatus='sent'
and d.alternatedeal ='no'
and d.manualdeal='no'
group by t.dealid

-----------count deals for each appointment time -----------

select count(distinct d.dealid) as testdrive,

case t.timeofday 

when 0 then 'Morning'

when 1 then 'Afternoon'

when 2 then 'Evening'

end as timeofday 

from #maxcreatedon m
inner join [MakeMyDeal_com].[dbo].[TestDrivelocal] t on m. dealid=t.dealid

inner join Mmd_feed..dealview d on t.dealid=d.dealid 

inner join Mmd_feed..dart dt on dt.dealerid=d.dealerid


where 

cast(d.submittedtimestamp as date)>=@StartDate and cast(d.submittedtimestamp as date)<=@EndDate

and d.dealstatus='sent'

and d.alternatedeal ='no'

and d.manualdeal='no'
and t.createdon = m.maxcreatedon

group by
case t.timeofday 
when 0 then 'Morning'
when 1 then 'Afternoon'
when 2 then 'Evening' end
order by 
case when t.timeofday = 'Morning'then 1
                 when t.timeofday= 'Afternoon' then 2
				 when t.timeofday ='Evening' then 3 end

Open in new window

tanj1035Asked:
Who is Participating?
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Why not a simple order by t.timeofday ?
0
tanj1035Author Commented:
If I do

group by
case t.timeofday
when 0 then 'Morning'
when 1 then 'Afternoon'
when 2 then 'Evening' end
order by t.timeofday  asc

It still gets the error message
Column "MakeMyDeal_com.dbo.TestDrivelocal.TimeOfDay" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you please run the following just for sanity check?
select count(distinct d.dealid) as testdrive,
	t.timeofday 
from #maxcreatedon m
	inner join [MakeMyDeal_com].[dbo].[TestDrivelocal] t on m. dealid=t.dealid
	inner join Mmd_feed..dealview d on t.dealid=d.dealid 
	inner join Mmd_feed..dart dt on dt.dealerid=d.dealerid
where 
	cast(d.submittedtimestamp as date)>=@StartDate and cast(d.submittedtimestamp as date)<=@EndDate
	and d.dealstatus='sent'
	and d.alternatedeal ='no'
	and d.manualdeal='no'
	and t.createdon = m.maxcreatedon
group by t.timeofday 
order by t.timeofday 

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You can use the positional number of the result column instead, which is 2, to supply in ORDER BY. No complaints then, but it will sort the result: Afternoon, Evening, Morning, and that is certainly not as intended.
IMO you should be able to group by t.timeofday, as shown above, and use the case only in the SELECT clause.
0
tanj1035Author Commented:
Hi Vitor, the query run out the right order, but how can I show "morning" "afternoon" "evening"?


testdrive       timeofday
39                            0
132                            1
41                            2
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this:
----AppointmentTime Testing----

declare @StartDate date
declare @EndDate date
set @StartDate = '2015-06-1'
set @EndDate ='2015-6-30'

-------Get the maxcreatedon for each dealid--
select t.dealid, max(createdon) as maxcreatedon
into #maxcreatedon
from [MakeMyDeal_com].[dbo].[TestDrivelocal] t
	inner join Mmd_feed..dealview d on t.dealid=d.dealid 
	inner join Mmd_feed..dart dt on dt.dealerid=d.dealerid
where d.dealstatus='sent'
	and d.alternatedeal ='no'
	and d.manualdeal='no'
group by t.dealid

-----------count deals for each appointment time -----------
;WITH CTE_Deals(testdrive,timeofday)
AS (
	select count(distinct d.dealid), 
		case t.timeofday 
			 when 0 then 'Morning'
			 when 1 then 'Afternoon'
			 when 2 then 'Evening' 
		end 
	from #maxcreatedon m
		inner join [MakeMyDeal_com].[dbo].[TestDrivelocal] t on m. dealid=t.dealid
		inner join Mmd_feed..dealview d on t.dealid=d.dealid 
		inner join Mmd_feed..dart dt on dt.dealerid=d.dealerid
	where 
		cast(d.submittedtimestamp as date)>=@StartDate and cast(d.submittedtimestamp as date)<=@EndDate
		and d.dealstatus='sent'
		and d.alternatedeal ='no'
		and d.manualdeal='no'
		and t.createdon = m.maxcreatedon
	group by 	
		case timeofday 
			 when 0 then 'Morning'
			 when 1 then 'Afternoon'
			 when 2 then 'Evening' 
		end 
	)

SELECT * 
FROM CTE_Deals
ORDER BY timeofday

Open in new window

0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Vitor, that has the "wrong sort order issue" I posted about.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Oh, yes. You're right. Will order alphabetically (Afternoon, Evening, Morning).
0
tanj1035Author Commented:
hi Qlemo,

Can you show me the modified query to help me to understand it correctly? Thanks.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Would be more easy if something like this:
case timeofday
       when 0 then 'AM'
       when 1 then 'Midday'
       when 2 then 'PM'
end

Or
case timeofday
       when 0 then '0 - Morning'
       when 1 then '1 - Afternoon'
       when 2 then '2 - Evening'
end
0

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
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Didn't try, but taking over Vitor's suggestion:
----AppointmentTime Testing----

declare @StartDate date
declare @EndDate date
set @StartDate = '2015-06-1'
set @EndDate ='2015-6-30'

-------Get the maxcreatedon for each dealid--
select t.dealid, max(createdon) as maxcreatedon
into #maxcreatedon
from [MakeMyDeal_com].[dbo].[TestDrivelocal] t
	inner join Mmd_feed..dealview d on t.dealid=d.dealid 
	inner join Mmd_feed..dart dt on dt.dealerid=d.dealerid
where d.dealstatus='sent'
	and d.alternatedeal ='no'
	and d.manualdeal='no'
group by t.dealid

-----------count deals for each appointment time -----------
;WITH CTE_Deals(testdrive,timeofday,TODtext)
AS (
	select count(distinct d.dealid), t.timeofday,
		case t.timeofday 
			 when 0 then 'Morning'
			 when 1 then 'Afternoon'
			 when 2 then 'Evening' 
		end 
	from #maxcreatedon m
		inner join [MakeMyDeal_com].[dbo].[TestDrivelocal] t on m. dealid=t.dealid
		inner join Mmd_feed..dealview d on t.dealid=d.dealid 
		inner join Mmd_feed..dart dt on dt.dealerid=d.dealerid
	where 
		cast(d.submittedtimestamp as date)>=@StartDate and cast(d.submittedtimestamp as date)<=@EndDate
		and d.dealstatus='sent'
		and d.alternatedeal ='no'
		and d.manualdeal='no'
		and t.createdon = m.maxcreatedon
	group by t.timeofday	
	)

SELECT testdrive, TODtext
FROM CTE_Deals
ORDER BY timeofday

Open in new window

0
Anoo S PillaiCommented:
Even-though the question is closed, proposing one more suggestions. Would be great if you could acknowledge whether this work or not !

Simple change the code snippet in your code FROM
group by
case t.timeofday 
when 0 then 'Morning'
when 1 then 'Afternoon'
when 2 then 'Evening' end
order by 
case when t.timeofday = 'Morning'then 1
                 when t.timeofday= 'Afternoon' then 2
				 when t.timeofday ='Evening' then 3 end

Open in new window

TO
group by  t.timeofday
order by  t.timeofday  

Open in new window

0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
... my saying ;-),
0
tanj1035Author Commented:
I tried  your query. The order is showing correctly in sql (morning afternoon, evening), but not in the SSRS (Morning, evening, afternoon). I do know why it is.
0
Anoo S PillaiCommented:
@tanj1035 - Is the previous comment from you a reply to my post  ?
0
tanj1035Author Commented:
Yes, I was replying your post, Anoo.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.