tanj1035
asked on
SQL, How to query Max in having clause
Hi Experts,
I got an error message from the query below. Can you help me on it? Thanks.
Column 'MakeMyDeal_com.dbo.TestDr ivelocal.C reatedOn' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
I got an error message from the query below. Can you help me on it? Thanks.
Column 'MakeMyDeal_com.dbo.TestDr
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
[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
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'
group by
case t.timeofday when 0 then 'Morning'
when 1 then 'Afternoon'
when 2 then 'Evening' end
having t.createdon= max(createdon)
ASKER
hi , thanks for your reply. I have tried your query, but it does not work to my case. It returned only.
TestDrive TimeofDay
1 Afternoon
TestDrive TimeofDay
1 Afternoon
ASKER
Since each dealID may have multiple "createdon" & "timeofday", I want to use the max "createdon" to pullout "timeofday". That is the purpose. Thanks.
Please post some sample date and the desired output. I don't understand your requirement..
Try this if this is what you need.
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
[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
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'
group by
case t.timeofday when 0 then 'Morning'
when 1 then 'Afternoon'
when 2 then 'Evening' end
having max(createdon) in (select createdon from [MakeMyDeal_com].[dbo].[TestDrivelocal])
ASKER
Sure, I posted the sample data of major table " makemydeal_com..testdrivel ocal".
As you see , for the DealID 25026, it has 2 rows. I want to use its max( createdon) which is "2015-06-04 12:58" to pull out "time of day" which is "0".
The same concept to other DealIDs, I want to use its max(createdon) to pull out the value of "time of day", then count for each datename, how many total dealID.
Hope my explanation is clear to you. Thanks.
Book2.xlsx
As you see , for the DealID 25026, it has 2 rows. I want to use its max( createdon) which is "2015-06-04 12:58" to pull out "time of day" which is "0".
The same concept to other DealIDs, I want to use its max(createdon) to pull out the value of "time of day", then count for each datename, how many total dealID.
Hope my explanation is clear to you. Thanks.
Book2.xlsx
ASKER
Hi Deepark,
Thanks for your reply.
The max (createdon) in your query seems not working in my case. I still got 2 " createdon" values returned for the DealID 25026. Please see the sample data in my previous post. Thanks.
Thanks for your reply.
The max (createdon) in your query seems not working in my case. I still got 2 " createdon" values returned for the DealID 25026. Please see the sample data in my previous post. Thanks.
date is same but time different for this ID
25026 NULL 6/5/15 10:00 PM 1 6/4/15 12:53 PM 20555 2
25026 NULL 6/4/15 10:00 AM 1 6/4/15 12:58 PM 20555 0
having convert(varchar(10),max(cr eatedon), 102) in (select convert(varchar(10),create don, 102) from [MakeMyDeal_com].[dbo].[Te stDriveloc al])
25026 NULL 6/5/15 10:00 PM 1 6/4/15 12:53 PM 20555 2
25026 NULL 6/4/15 10:00 AM 1 6/4/15 12:58 PM 20555 0
having convert(varchar(10),max(cr
ASKER
Sorry, the DealID 25206 still returns 2 rows even I convert the "createon".
I think the issue is in the beginning of my query, the distinct d.dealID pulls out 2 "createdon", even we use MAX. But I have to keep distinct d.dealID.
Any solutions? Thanks.
I think the issue is in the beginning of my query, the distinct d.dealID pulls out 2 "createdon", even we use MAX. But I have to keep distinct d.dealID.
Any solutions? Thanks.
I'm a little confused. It looks like you're wanting to group by timeofday (represented as morning, afternoon, or evening) and with the sample data, I would expect to see something like
3 Morning
3 Afternoon
6 Evening
However, your expected results are showing counts that don't match the sample data and group by the [day created on]. What is it you really want? Some real data and precise expected results would be a great help.
3 Morning
3 Afternoon
6 Evening
However, your expected results are showing counts that don't match the sample data and group by the [day created on]. What is it you really want? Some real data and precise expected results would be a great help.
ASKER
Yes, I want to know that
Time count(dealID)
morning 3
afternoon 3
evening 6.
In order to count a correct number of dealID, I have to only count the DealID with max (createdon) since 1 dealID may have multiple createdon.
Hope this can give you a better explanation. I also attached the excel with all tables. Thanks for your help.
Copy-of-Book2.xlsx
Time count(dealID)
morning 3
afternoon 3
evening 6.
In order to count a correct number of dealID, I have to only count the DealID with max (createdon) since 1 dealID may have multiple createdon.
Hope this can give you a better explanation. I also attached the excel with all tables. Thanks for your help.
Copy-of-Book2.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please check your expected result in the Excel file:
Expected Result
DealID Count Day of Createdon
30 Sun
20 Monday
32 Tues
20 Wed
11 Thur
12 Fri
15 Sat
1 Sun
That is different from what you're saying here:
Expected Result
DealID Count Day of Createdon
30 Sun
20 Monday
32 Tues
20 Wed
11 Thur
12 Fri
15 Sat
1 Sun
That is different from what you're saying here:
Yes, I want to know that
Time count(dealID)
morning 3
afternoon 3
evening 6.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window