MapInfo Pro

 View Only
Expand all | Collapse all

Group by Year - SQL

  • 1.  Group by Year - SQL

    Posted 04-02-2020 11:02
    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
    ------------------------------


  • 2.  RE: Group by Year - SQL

    Employee
    Posted 04-03-2020 02:38
    Edited by Peter Møller 04-03-2020 09:06
    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) "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
    ------------------------------



  • 3.  RE: Group by Year - SQL

    Posted 04-03-2020 06:26
    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
    ------------------------------



  • 4.  RE: Group by Year - SQL

    Employee
    Posted 04-03-2020 06:42
    Edited by Peter Møller 04-03-2020 09:07
    Depends on exactly what you are looking for, Caroline.

    Here's two suggestions:
    Select Year(CreatedDate) "YearCreated", Month(CreatedDate) "MonthCreated", Count(*) "Counts"
    From SomeTable
    Group By YearCreated, MonthCreated
    Order By YearCreated, MonthCreated
    Into Selection

    Select Str$(Year(CreatedDate)) + "-" + Str$(Month(CreatedDate)) "YearMonthCreated", Count(*) "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
    ------------------------------



  • 5.  RE: Group by Year - SQL

    Posted 04-03-2020 07:07
    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
    ------------------------------



  • 6.  RE: Group by Year - SQL

    Employee
    Posted 04-03-2020 07:34
    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
    ------------------------------



  • 7.  RE: Group by Year - SQL

    Posted 04-03-2020 07:56
    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
    ------------------------------



  • 8.  RE: Group by Year - SQL

    Employee
    Posted 04-03-2020 08:48
    https://communitydownloads.pbinsight.com/code-exchange/download/query-date-and-time-x64-only
    Tool may be useful in future for doing Date & Time queries

    ------------------------------
    Bill Wemple
    Principal QA Engineer
    Pitney Bowes
    Troy, NY
    ------------------------------



  • 9.  RE: Group by Year - SQL

    Employee
    Posted 04-03-2020 09:06
    Good point, Bill.

    That tool can also be found on the MapInfo Marketplace


    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------



  • 10.  RE: Group by Year - SQL

    Posted 04-03-2020 09:43
    Useful - thanks Bill!

    ------------------------------
    Caroline Hilton
    Pelican GeoGraphics Ltd (VAR)
    Petersfield
    ------------------------------



  • 11.  RE: Group by Year - SQL

    Employee
    Posted 04-03-2020 09:08
    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
    ------------------------------



  • 12.  RE: Group by Year - SQL

    Posted 04-03-2020 10:06
    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
    ------------------------------



  • 13.  RE: Group by Year - SQL

    Posted 04-03-2020 10:21
    Good point Eric - thanks for that, yes of course, it doesn't matter how it's displayed, does it? That's set up by the computer settings not MapInfo :)

    ------------------------------
    Caroline Hilton
    Pelican GeoGraphics Ltd (VAR)
    Petersfield
    ------------------------------