I have a table ExchangeUserMailbox
with columns
- OrganisationID (GUID)
- MailboxLocationName (STRING)
- MailboxCode(STRING)
- DayofMon (INT)
- UPN(STRING)
The possible values for DayofMon
are "15" and "30" (I have data coming to this table on every two days of month.. eg:- 14 th and 30th , or 15th and 31st )
I am running the following SQL script
SELECT
[OrganisationID]
,[MailboxLocationName] AS Location
,MailboxCode as LocationCode
,DayofMon
,COUNT(DISTINCT [UPN]) AS [Count]
FROM
[ExchangeUserMailbox]
GROUP BY
[OrganisationID], [MailboxLocationName], DayofMon, MailboxCode
HAVING
DayofMon = MAX(DayofMon)
My intention is to get all records with the maximum value of DayofMon
and aggregate the count of UPNs.
But in the output window I am getting
OrganisationID Location LocationCode DayofMon Count
2EC8650F-8902-4CFB-BE0E-A218982EDEEC Diffraction DIF 1 3
2EC8650F-8902-4CFB-BE0E-A218982EDEEC Diffraction DIF 30 10
As you can see it aggregated dayofmon= 1
and dayofmon=30
. But I want to aggregate only dayofmon=30
What am I doing wrong here?
4条答案
按热度按时间ia2d9nvy1#
Try a hard_code ... having DayofMon > 15 ... I'm more of an Oracle guy, so from my experience there you can't put a Windowed function in the result of Having.
mwngjboj2#
Try this: use max function on select.
wtlkbnrh3#
The HAVING clause is designed for for filtering by aggregated calculations, hence it is performed AFTER the GROUP BY is complete so it can access the aggregated values. What you need requires filtering BEFORE grouping is performed, so the having clause isn't helpful.
You could just filter the data where DayOfMon is in (28, 29, 30 or 31)
or you could make use of ROW_NUMBER() like this:
Which will give the number 1 to each row having the largest DayOfMon for each "partition"
jslywgbw4#
The most straightforward way is using a subquery:
The "hard coded" solutions work, but are more brittle. This way, you are sure to always get the records with the highest DayofMon value (even if it is an unexpected value) and you get the actual value of DayofMon instead of a hard coded value.