not complete SQL output-CodePudding

If I write:

SELECT * FROM `expense`

I'll get

2022-02-17 10 bread
2022-02-23 20 perfume

but if I put this:

SELECT expense_date, amount, description, sum(amount) as total
FROM expense

The query gives me the LAST item. How do I get ALL items like I get with

SELECT *

I tried

SELECT *, sum(amount) as total
FROM expense

but no dice :(

CodePudding user response：

Well you could use SUM() as an analytic function with a window over the entire table:

SELECT expense_date, amount, description, SUM(amount) OVER () AS total
FROM expense;

This would output:

expense_date | amount | description | total
2022-02-17   | 10     | bread       | 30
2022-02-23   | 20     | perfume     | 30

CodePudding user response：

SUM() is an aggregate function. Summing only one record is no sense.

See group by to get the total amount of different date.

SELECT 
    expense_date,
    description, 
    sum(amount) as total
FROM
    expense
GROUP BY expense_date,description
ORDER BY description;