在Oracle中按月分组 [英] Group by month in Oracle

问题描述

我正在尝试按月在oracle中分组，但是我在"YEAR"函数上收到无效的标识符，不确定原因.

这是我的代码:

SELECT CAST(MONTH(day_date) AS VARCHAR(2)) + '-' + CAST(YEAR(day_date) AS VARCHAR(4)) AS MY_DATE,
sum(cash_sales) as cash_sales, sum(unit_sales) as unit_sales
FROM NC_SALES_CAT_TL
GROUP BY CAST(MONTH(day_date) AS VARCHAR(2)) + '-' + CAST(YEAR(day_date) AS VARCHAR(4))

如何按月完成所需的分组?

解决方案

Oracle中的串联运算符为||.不是+:

SQL> select 'a' +' b' from dual;
select 'a' +' b' from dual
       *
ERROR at line 1:
ORA-01722: invalid number

SQL> select 'a' || 'b' from dual;

'A
--
ab

---

此外， YEAR 是MySQL函数.在Oracle中，您将使用 EXTRACT(YEAR FROM ....)

---

最后，您是否知道 TO_CHAR

因此，您应该将整个查询重写为:

SELECT TO_CHAR(day_date, 'MM-YYYY') AS MY_DATE,
       sum(cash_sales) as cash_sales, sum(unit_sales) as unit_sales
FROM NC_SALES_CAT_TL
GROUP BY TO_CHAR(day_date, 'MM-YYYY')

I'm trying to group by month in oracle, but I'm getting an invalid identifier on the "YEAR" function, not sure why.

Here is my code:

SELECT CAST(MONTH(day_date) AS VARCHAR(2)) + '-' + CAST(YEAR(day_date) AS VARCHAR(4)) AS MY_DATE,
sum(cash_sales) as cash_sales, sum(unit_sales) as unit_sales
FROM NC_SALES_CAT_TL
GROUP BY CAST(MONTH(day_date) AS VARCHAR(2)) + '-' + CAST(YEAR(day_date) AS VARCHAR(4))

How can I accomplish the desired grouping by month?

解决方案

Concatenation operator in Oracle is ||. Not +:

SQL> select 'a' +' b' from dual;
select 'a' +' b' from dual
       *
ERROR at line 1:
ORA-01722: invalid number

SQL> select 'a' || 'b' from dual;

'A
--
ab

---

In addition, YEAR is a MySQL function. In Oracle, you will use EXTRACT(YEAR FROM ....)

---

Finally, are you aware of the TO_CHAR function?

So you should rewrite your whole query as:

SELECT TO_CHAR(day_date, 'MM-YYYY') AS MY_DATE,
       sum(cash_sales) as cash_sales, sum(unit_sales) as unit_sales
FROM NC_SALES_CAT_TL
GROUP BY TO_CHAR(day_date, 'MM-YYYY')

这篇关于在Oracle中按月分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！