May 29, 2018 at 3:15 pm
SELECT
DATENAME (MONTH, [Date]) AS 'MONTH'
-- , Replace([notes], 'Attended - ','' ) As 'Class Name'
, --DATENAME(Day,[Date]) AS 'Day Of The Month',
count(*) AS 'Attendance'
FROM
dbo.Logs
Where
[notes] like '%Attended%%' and Notes Like '%adult%'
and Year([Date]) = '2017'
GROUP BY
[Date]
--, Notes--,
--datepart(Day,[Date])
ORDER BY
MONTH([Date])
-- ,datepart(DD,[Date])
;
Returns:
MONTH Attendance
January 12
January 7
January 10
February 10
February 9
February 10
etc...
What I want is to group by month, so my result set only has 12 rows, however if I add a 'Distinct' clause, I get an error message,
"Order by items must appear in the select list if select distinct is specified"
Schema/Sample:
SET ANSI_NULLS ON
GO
TIA
May 29, 2018 at 3:26 pm
Yep, that's not gonna work, SQL needs the sort column in the data. Here's one workaround:
SELECT MONTH, Attendance
FROM (
SELECT
DATENAME (MONTH, [Date]) AS 'MONTH',
MAX(DATEPART (MONTH, [Date])) AS 'Month#',
COUNT(*) AS 'Attendance'
FROM
dbo.Logs
Where
[notes] like '%Attended%%' and Notes Like '%adult%'
and [Date] >= '20170101' and [Date] < '20180101'
GROUP BY
DATENAME (MONTH, [Date])
) AS derived
ORDER BY
Month#
;
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 29, 2018 at 3:32 pm
JaybeeSQL - Tuesday, May 29, 2018 3:15 PMHi all,
SELECT
DATENAME (MONTH, [Date]) AS 'MONTH'
-- , Replace([notes], 'Attended - ','' ) As 'Class Name'
, --DATENAME(Day,[Date]) AS 'Day Of The Month',
count(*) AS 'Attendance'
FROM
dbo.Logs
Where
[notes] like '%Attended%%' and Notes Like '%adult%'
and Year([Date]) = '2017'
GROUP BY
[Date]
--, Notes--,
--datepart(Day,[Date])
ORDER BY
MONTH([Date])
-- ,datepart(DD,[Date])
;Returns:
MONTH Attendance
January 12
January 7
January 10
February 10
February 9
February 10
etc...What I want is to group by month, so my result set only has 12 rows, however if I add a 'Distinct' clause, I get an error message,
"Order by items must appear in the select list if select distinct is specified"Schema/Sample:
CREATE TABLE [dbo].[logs](
[id] [int] NOT NULL,
[profileId] [int] NULL,
[logId] [int] NULL,
[date] [date] NOT NULL,
[time] [datetime] NULL,
[notes] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
INSERT INTO #logs VALUES
MONTH Attendance
('January' '12')
('January' '7')
('January' '10')
('February' '10')
('February' '9')
('February' '10')
SELECT * FROM #logsTIA
Just curious, but have you tested the code you posted in an empty sandbox database?
Nothing posted will help us with your question.
May 30, 2018 at 6:31 am
I suspect your problem is going to be in how your [DATE] field is stored.
Would I be correct in presuming that field is stored as either a DATE or DATETIME?
May 30, 2018 at 7:33 am
JaybeeSQL - Tuesday, May 29, 2018 3:15 PM
What I want is to group by month, so my result set only has 12 rows, however ...
TIA
If you want to group by MONTH, then why don't you in the query?
Shouldn'tGROUP BY
[Date]
beGROUP BY
MONTH([Date])
May 30, 2018 at 11:47 am
HanShi - Wednesday, May 30, 2018 7:33 AMJaybeeSQL - Tuesday, May 29, 2018 3:15 PM
What I want is to group by month, so my result set only has 12 rows, however ...
TIAIf you want to group by MONTH, then why don't you in the query?
Shouldn't
GROUP BY
[Date]
beGROUP BY
MONTH([Date])
What will you do when the span of the query passes through the end of a calendar year? While you might not have to worry about getting unique values, you may have to worry about the order in which they appear. More importantly, what do you do when the time span is longer than a year? That will destroy your query's accuracy.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 30, 2018 at 12:46 pm
HanShi - Wednesday, May 30, 2018 12:39 PM@steve-2: Agree, but the original query filters on one year. That makes my suggestion valid for this query. It may not be the case when different specs are required.
Yeah, I know... My point was that future maintainability MAY be affected, and doing it right the first time avoids one heck of lot of work later, when the original has been repeatedly changed and enhanced over the course of a couple of years, and is then more important than it is now, and all of a sudden, business wants year over year, so two years worth of data are needed, and now you've got to redesign the whole shootin' match all because you "got lazy" up front...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 31, 2018 at 1:08 pm
ScottPletcher - Tuesday, May 29, 2018 3:26 PMYep, that's not gonna work, SQL needs the sort column in the data. Here's one workaround:
SELECT MONTH, Attendance
FROM (
SELECT
DATENAME (MONTH, [Date]) AS 'MONTH',
MAX(DATEPART (MONTH, [Date])) AS 'Month#',
COUNT(*) AS 'Attendance'
FROM
dbo.Logs
Where
[notes] like '%Attended%%' and Notes Like '%adult%'
and [Date] >= '20170101' and [Date] < '20180101'
GROUP BY
DATENAME (MONTH, [Date])
) AS derived
ORDER BY
Month#;
That hit the spot - pretty nifty workaround, using a subquery!! I think that's what separates the DBA trying his hand at Dev , from the dedicated Dev.
Any idea how to change 'month' to days of the year, and display the actual date? I tried 'Day', which only gets me 31 rows, in fact the correct number is 176 (as evinced by a select (distinct date)), using 'DayoftheYear' yields 176 rows, but displayed as integers from 1-365.
- Sample data/schema - heads up acknowledged, will correct for future posts.
- As above, the datatype for logs.date is Date.
May 31, 2018 at 2:59 pm
Am I missing something, or would it be a lot easier as:
select
datename(month,[date]),
count(*)
from logs
group by
year([date]),
datename(month,[date])
order by
year([date]),
datename(month,[date])
or just plain:
select
[date],
count(*)
from logs
group by
[date]
order by
[date]
If you actually want a daily breakdown?
May 31, 2018 at 3:02 pm
JaybeeSQL - Thursday, May 31, 2018 1:08 PMScottPletcher - Tuesday, May 29, 2018 3:26 PMYep, that's not gonna work, SQL needs the sort column in the data. Here's one workaround:
SELECT MONTH, Attendance
FROM (
SELECT
DATENAME (MONTH, [Date]) AS 'MONTH',
MAX(DATEPART (MONTH, [Date])) AS 'Month#',
COUNT(*) AS 'Attendance'
FROM
dbo.Logs
Where
[notes] like '%Attended%%' and Notes Like '%adult%'
and [Date] >= '20170101' and [Date] < '20180101'
GROUP BY
DATENAME (MONTH, [Date])
) AS derived
ORDER BY
Month#;
That hit the spot - pretty nifty workaround, using a subquery!! I think that's what separates the DBA trying his hand at Dev , from the dedicated Dev.
Any idea how to change 'month' to days of the year, and display the actual date? I tried 'Day', which only gets me 31 rows, in fact the correct number is 176 (as evinced by a select (distinct date)), using 'DayoftheYear' yields 176 rows, but displayed as integers from 1-365.
- Sample data/schema - heads up acknowledged, will correct for future posts.
- As above, the datatype for logs.date is Date.
LOL, I've been 100% full-time DBA since 1986. I know SQL/T-SQL, not other development.
SELECT
DATEADD(DAY, DATEDIFF(DAY, 0, Date), Date) AS Day,
COUNT(*) AS 'Attendance'
FROM
dbo.Logs
WHERE
[notes] like '%Attended%%' and Notes Like '%adult%'
and [Date] >= '20170101' and [Date] < '20180101'
GROUP BY
DATEADD(DAY, DATEDIFF(DAY, 0, Date), Date)
ORDER BY
Day
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 31, 2018 at 5:36 pm
ScottPletcher - Thursday, May 31, 2018 3:02 PMJaybeeSQL - Thursday, May 31, 2018 1:08 PMScottPletcher - Tuesday, May 29, 2018 3:26 PMYep, that's not gonna work, SQL needs the sort column in the data. Here's one workaround:
SELECT MONTH, Attendance
FROM (
SELECT
DATENAME (MONTH, [Date]) AS 'MONTH',
MAX(DATEPART (MONTH, [Date])) AS 'Month#',
COUNT(*) AS 'Attendance'
FROM
dbo.Logs
Where
[notes] like '%Attended%%' and Notes Like '%adult%'
and [Date] >= '20170101' and [Date] < '20180101'
GROUP BY
DATENAME (MONTH, [Date])
) AS derived
ORDER BY
Month#;
That hit the spot - pretty nifty workaround, using a subquery!! I think that's what separates the DBA trying his hand at Dev , from the dedicated Dev.
Any idea how to change 'month' to days of the year, and display the actual date? I tried 'Day', which only gets me 31 rows, in fact the correct number is 176 (as evinced by a select (distinct date)), using 'DayoftheYear' yields 176 rows, but displayed as integers from 1-365.
- Sample data/schema - heads up acknowledged, will correct for future posts.
- As above, the datatype for logs.date is Date.LOL, I've been 100% full-time DBA since 1986. I know SQL/T-SQL, not other development.
SELECT
DATEADD(DAY, DATEDIFF(DAY, 0, Date), Date) AS Day,
COUNT(*) AS 'Attendance'
FROM
dbo.Logs
WHERE
[notes] like '%Attended%%' and Notes Like '%adult%'
and [Date] >= '20170101' and [Date] < '20180101'
GROUP BY
DATEADD(DAY, DATEDIFF(DAY, 0, Date), Date)
ORDER BY
Day
That nearly does the trick, however the result yields
2134-01-04 6
2134-01-06 13
2134-01-08 9
2134-01-10 16
2134-01-18 8
Not sure where the '2134/5' comes from. Though both rowcounts are the same, Andy's 'Day' reply yields
2017-01-02 6
2017-01-03 13
2017-01-04 9
2017-01-05 16
2017-01-09 8
1986????!! !!! Sweeeeet Jesus. That explains everything. I was barely old enough to slobber over the lead singer of The Bangles in '86...
Btw , I noticed I seem to be learning a lot from you lately. Thanks for that 🙂
May 31, 2018 at 8:04 pm
JaybeeSQL - Thursday, May 31, 2018 5:36 PMNot sure where the '2134/5' comes from. Though both rowcounts are the same, Andy's 'Day' reply yields
I suspect he was aiming for DATEADD(DAY, DATEDIFF(DAY, 0, [Date]), 0) and missed. 😉 It's an old school trick from the pre-SQL 2008 days that is equivalent to Cast([Date] As Date) in that it strips the time portion off a DateTime type. You don't actually need it at all in your case since your [Date] column is actually a Date type already and so won't have a Time portion to worry about, which is why it looks right in my results.
And trying to write that sentence alone is a good reason to never name your columns [Date]! (Or any other SQL reserved word)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply