How group by month-CodePudding

I'm having trouble finding out how to group by month-year. The below has error,

Not a group by expression

select 
    dcst.stateid as stateId,
    dcst.crtdt ,
    to_char(to_date(DCST.CRTDT,'DD-MON-YY HH.MI.SS."000000000" AM'),'Month') AS Month,
    to_date(DCST.CRTDT,'DD-MON-YY HH.MI.SS."000000000" AM') as datetimes
from
    TBL dcst
group by to_char(to_date(DCST.CRTDT,'MON-YY HH.MI.SS."000000000" AM'),'MON-YY')

This is an example of the dates in the table (the result of the above query without the group by):

Stateid   CRTDT                            MONTH     DATETIMES
506       22-DEC-22 06.08.17.480000000 PM  December  22-DEC-22
506       23-DEC-22 10.11.00.795000000 AM  December  23-DEC-22
853       10-JAN-23 12.30.45.212000000 AM  January   04-JAN-23

I seem to be doing what this thread suggests is correct for group by month/year.

I'm testing in Oracle Sql Developer, but eventually will be testing on a Microsoft SQL db that isn't easily accessed on devices.

Update: I added the StateId that I want to do a count by month, but doing a count by that gives the error "not a single-group group function". This error happens without the group by month part added with it.

Update2: I'm trying to remove extra things from my query and get 'Group function is not allowed here'.

select 
    dcst.stateid as stateId,
    dcst.crtdt 
from tbl dcst
group by count(dcst.stateid), to_char(to_date(DCST.CRTDT,'MON-YY HH.MI.SS."000000000" AM'),'MON-YY')

expected output would be (I think):

CodePudding user response：


CREATE TABLE dcst
(STATEID, CRTDT) AS
SELECT 506, TIMESTAMP '2022-12-22 06:08:17.480000' FROM DUAL UNION ALL
SELECT 506, TIMESTAMP'2022-12-23 10:11:00.480000' FROM DUAL UNION ALL
SELECT 853,TIMESTAMP'2023-01-10 10:11:00.480000' FROM DUAL


select to_char(crtdt, 'YYYY/MM'), 
       count(*)
from dcst
group by to_char(crtdt, 'YYYY/MM')
order by to_char(crtdt, 'YYYY/MM')

TO_CHAR(CRTDT,'YYYY/MM')    COUNT(*)
2022/12    2
2023/01    1