sqlweb-sql

Group by year without year() function [Web-SQL]

I want to group by year and count all orders from each year.

I found this: SQL grouping by month and year but all the answers use functions that don't work in WebSQL.

I tried to run this:

SELECT to_char (OrderDate, 'YYYY') AS y, count(*) ordCnt // and year
FROM [Orders]
group by OrderDate
order by ordCnt desc

Here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all but there's no to_char function.

I also tried this:

SELECT Date(OrderDate, '%Y') AS y, count(*) ordCnt
FROM [Orders]
group by y

But it produces nulls only.

Solution

  • Web-SQL is really SQLite. I would expect the following to work:

    SELECT strftime(OrderDate, '%Y') AS y, count(*) as ordCnt
FROM Orders o
GROUP BY strftime(OrderDate, '%Y');