April 17, 2018 at 1:02 pm
I have an (NVarchar) column called 'Notes' . here's some sample data..
Attended - Monday Adult
Attended - Monday Adult
Attended - Monday Inter/
Attended - Monday Inter/
Attended - Tuesday Adult
So, the desired output should look like this..
Here's the monkey's breakfast I've made of it so far...
SELECT Replace(Date, '00:00:00.0000000','' ) As 'Date', COUNT(DISTINCT notes) As 'Class' , Count (Distinct 'Class') As Attendees--
,Notes AS [Type] --(Apart from the date which maybe needed to group, I think the entire Select section needs rework)
From Logs
Where Notes like '%Attended -%' --and Date like '2011%'
Group by date
--Order by 'Attendees' desc
TIA 🙂
April 17, 2018 at 1:07 pm
JaybeeSQL - Tuesday, April 17, 2018 1:02 PMHi all
This seemingly simple piece of code has thrown me - (and I've been working with TSQL a few years!!)I have an (NVarchar) column called 'Notes' . here's some sample data..
Attended - Monday Adult
Attended - Monday Adult
Attended - Monday Inter/
Attended - Monday Inter/
Attended - Tuesday AdultSo, the desired output should look like this..
'
Attended - Monday Adult -2
Attended - Monday Inter/ - 2
Attended - Tuesday Adult - 1
Etc....Here's the monkey's breakfast I've made of it so far...
SELECT Replace(Date, '00:00:00.0000000','' ) As 'Date', COUNT(DISTINCT notes) As 'Class' , Count (Distinct 'Class') As Attendees--
,Notes AS [Type] --(Apart from the date which maybe needed to group, I think the entire Select section needs rework)
From Logs
Where Notes like '%Attended -%' --and Date like '2011%'
Group by date
--Order by 'Attendees' descTIA 🙂
Not only have you been working with SQL for a few years, you've been here long enough to know that you should provide your sample data in a consumable format, for the best response rate.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 17, 2018 at 1:31 pm
Phil Parkin - Tuesday, April 17, 2018 1:07 PMJaybeeSQL - Tuesday, April 17, 2018 1:02 PMHi all
This seemingly simple piece of code has thrown me - (and I've been working with TSQL a few years!!)I have an (NVarchar) column called 'Notes' . here's some sample data..
Attended - Monday Adult
Attended - Monday Adult
Attended - Monday Inter/
Attended - Monday Inter/
Attended - Tuesday AdultSo, the desired output should look like this..
'
Attended - Monday Adult -2
Attended - Monday Inter/ - 2
Attended - Tuesday Adult - 1
Etc....Here's the monkey's breakfast I've made of it so far...
SELECT Replace(Date, '00:00:00.0000000','' ) As 'Date', COUNT(DISTINCT notes) As 'Class' , Count (Distinct 'Class') As Attendees--
,Notes AS [Type] --(Apart from the date which maybe needed to group, I think the entire Select section needs rework)
From Logs
Where Notes like '%Attended -%' --and Date like '2011%'
Group by date
--Order by 'Attendees' descTIA 🙂
Not only have you been working with SQL for a few years, you've been here long enough to know that you should provide your sample data in a consumable format
Such as?
April 17, 2018 at 1:41 pm
JaybeeSQL - Tuesday, April 17, 2018 1:31 PMPhil Parkin - Tuesday, April 17, 2018 1:07 PMNot only have you been working with SQL for a few years, you've been here long enough to know that you should provide your sample data in a consumable formatSuch as?
I'm surprised you ask, given the number of points you have, but have a look at this random thread. It includes a runnable setup script, and desired results.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 17, 2018 at 5:12 pm
Resolution found, will post up.
Select Replace(notes, 'Attended - ','' ) As 'Class', NumberAttending
From
(
SELECT DISTINCT notes ,Count (notes) As NumberAttending
From logs
Where notes like '%attended
Group by notes
) As SortedTable
Order by NumberAttending desc
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply