Caroline,
The only thing I want to add to all this useful info from Peter and Bill is that you mentioned a few times that your date data was in "mm/dd/yyyy " format. Peter's answers worked because your data is truly in a date file (a very good thing) and not in a string field which some people still do.
My note here is that because you have a date column type, the format is irrelevant. In fact, your date is not stored as "mm/dd/yyyy " but actually as YYYYMMDD as digits and the display code in MapInfo Pro will present it to you and let you edit it according to the formatting rules of your system. The exact same table could be used by a colleague whose format was "dd--mm-yy" or anything else because the SQL is not parsing a string but looking at known data order without separators for the Year, Month and Day values.
It also sorts and groups much better that way.
(Strings are the devils work)
------------------------------
Eric Blasenheim
Spectrum Spatial Technical Product Manager
Troy, NY
------------------------------
Original Message:
Sent: 04-03-2020 09:08
From: Peter Horsbøll Møller
Subject: Group by Year - SQL
Sorry, just getting too used to the new SQL syntax in v2019 :-)
I have modified my answers above and removed the "As" keyword in front of the Column Alias.
------------------------------
Peter Horsbøll Møller
Distinguished Engineer
Pitney Bowes Software & Data
Original Message:
Sent: 04-03-2020 07:56
From: Caroline Hilton
Subject: Group by Year - SQL
Thanks Peter - got it. Needed to leave out the "as" from the sql box, of course! Much appreciated.
------------------------------
Caroline Hilton
Pelican GeoGraphics Ltd (VAR)
Petersfield
Original Message:
Sent: 04-03-2020 07:34
From: Peter Horsbøll Møller
Subject: Group by Year - SQL
Oh, I forgot to say that YearCreated should be replaced with your column holding the date.
If you run into more issues, share a screen dump of your SQL Select dialog
------------------------------
Peter Horsbøll Møller
Distinguished Engineer
Pitney Bowes Software & Data
Original Message:
Sent: 04-03-2020 07:07
From: Caroline Hilton
Subject: Group by Year - SQL
Hmm... not having much luck. I tried the "year" version in the SQL dialogue box, but it says "found [YearCreated] when search for [from].
I tried both the year and the 2 month-year versions in the MapBasic box and it says
Unrecognized command: Into
I'm using a very basic dataset for this, not the client's date, just trying to get it to work before sending it over...
------------------------------
Caroline Hilton
Pelican GeoGraphics Ltd (VAR)
Petersfield
Original Message:
Sent: 04-03-2020 06:41
From: Peter Horsbøll Møller
Subject: Group by Year - SQL
Depends on exactly what you are looking for, Caroline.
Here's two suggestions:
Select Year(CreatedDate) As "YearCreated", Month(CreatedDate) As "MonthCreated", Count(*) As "Counts"
From SomeTable
Group By YearCreated, MonthCreated
Order By YearCreated, MonthCreated
Into Selection
Select Str$(Year(CreatedDate)) + "-" + Str$(Month(CreatedDate)) As "YearMonthCreated", Count(*) As "Counts"
From SomeTable
Group By YearMonthCreated
Order By YearMonthCreated
Into Selection
Giving it another thought, it might actually give you kind of the same result just with the Year and Month in either one or two columns.
------------------------------
Peter Horsbøll Møller
Distinguished Engineer
Pitney Bowes Software & Data
Original Message:
Sent: 04-03-2020 06:26
From: Caroline Hilton
Subject: Group by Year - SQL
Thanks Peter. That's great. Do you know how I would also group by month&year? The client's data is held in mm-dd-yyy format.
Cheers in advance
Caroline
------------------------------
Caroline Hilton
Pelican GeoGraphics Ltd (VAR)
Petersfield
Original Message:
Sent: 04-03-2020 02:37
From: Peter Horsbøll Møller
Subject: Group by Year - SQL
Hi Caroline
That can be done. You can use the Year() function to extract the year from your Data column. And then use the calculated column in the Group By and potentially Order By part of your Select statement:
Select Year(CreatedDate) As "YearCreated", Count(*) As "Counts"
From SomeTable
Group By YearCreated
Order By YearCreated
Into Selection
I hope this makes sense
------------------------------
Peter Horsbøll Møller
Distinguished Engineer
Pitney Bowes Software & Data
Original Message:
Sent: 04-02-2020 11:01
From: Caroline Hilton
Subject: Group by Year - SQL
Hi all
Can any of you SQL experts (@PeterHM...?) advise me on how to group a table by year, or by month/year, using a date field formatted mm/dd/yyyy please?
It looks as though in true sql you would use DATEPART but I don't know if MapInfo recognises that command. At least I can't get it to in this context.
Many thanks.
------------------------------
Caroline Hilton
UK
------------------------------