July 15, 2020 at 1:58 pm
i have a table with a number 8 digits long. It represents a date but not a date column. ie 20200715. The table also has an amount column that i need to sum.
SELECT col_date / 10000 AS col_yr, SUM(col_amt) AS Expr1
FROM TABLE1
WHERE (col_type = 'R')
GROUP BY col_date / 10000, col_amt
ORDER BY col_yr
i am getting
2015, 10.00
2015, 15.00
2016, 12.00
2016, 20.00
but i need to group the col_yr together and total up the col_amt to look like
2015, 25.00
2016, 32.00
and so on
July 15, 2020 at 2:01 pm
Try changing your GROUP BY:
GROUP BY col_date / 10000
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 15, 2020 at 2:04 pm
ok that got it thank you. i was worried about aggregate errors i never tried that. So thank you very much.
July 15, 2020 at 2:06 pm
To add to what Phil is saying here, you only need to Group on the columns/expressions that aren't in an aggregate function. You are aggregating on Col_amt therefore it's very unlikely you need to Group on it.
GROUP BY will aggregate the results in each distinct group defined in the clause. You have col_date / 10000, col_amt in your GROUP BY, therefore each distinct set for col_date / 10000 and col_amt will be a group, and hence why you're getting the results you are.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply