计数group by with inner

Question

我的Oracle数据库中有两个相关的表: EXPENSETYPE和EXPENSETYPE。

我想要显示按费用类型分组的所有寄存器，即：

| Number  | Description  | Value    | Registers
| 99      | Car Rent     | $200     | 8
| 65      | Food         | $500     | 22

..。

我试过了：

SELECT
    ET.NUMBER,
    ET.DESCRYPTION,
    E.VALUE,
    E.DATE
    COUNT(E.ID) AS REGISTERS
  FROM EXPENSES E
    INNER JOIN EXPENSETYPE ET ON E.EXPENSE_ID= ET.ID
  GROUP BY ET.NUMBER
  ORDER BY 
    E.DATE, ET.NUMBER

但我得到了ORA-00979

Answer 1

您需要在GROUP BY中包含所有未聚合的列。然后你需要对日期做些什么。也许你打算：

SELECT ET.NUMBER, E.DESCRYPTION, E.VALUE,
       COUNT(E.ID) AS REGISTERS
FROM EXPENSES E INNER JOIN
     EXPENSETYPE ET
     ON E.EXPENSE_ID= ET.ID
GROUP BY ET.NUMBER, E.DESCRYPTION, E.VALUE
ORDER BY MIN(E.DATE), ET.NUMBER;