Cannot use group by and over(partition by) in the same query?

Question

I have a table myTable with 3 columns. col_1 is an INTEGER and the other 2 columns are DOUBLE. For example, col_1={1, 2}, col_2={0.1, 0.2, 0.3}. Each element in col_1 is composed of all the values of col_2 and col_2 has repeated values for each element in col_1. The 3rd column can have any value as shown below:

    col_1 | col_2 | Value
    ----------------------
    1     |  0.1  |  1.0
    1     |  0.2  |  2.0
    1     |  0.2  |  3.0
    1     |  0.3  |  4.0
    1     |  0.3  |  5.0
    2     |  0.1  |  6.0
    2     |  0.1  |  7.0
    2     |  0.1  |  8.0
    2     |  0.2  |  9.0
    2     |  0.3  |  10.0

What I want is to use an aggregate-function SUM() on the Value column partition by col_1 and grouped by col_2. The Above table should look like this:

    col_1 | col_2 | sum_value
    ----------------------
    1     |  0.1  |  1.0
    1     |  0.2  |  5.0
    1     |  0.3  |  9.0
    2     |  0.1  |  21.0
    2     |  0.2  |  9.0
    2     |  0.3  |  10.0

I tried the following SQL query:

SELECT col_1, col_2, sum(Value) over(partition by col_1) as sum_value
from myTable
GROUP BY col_1, col_2

But on DB2 v10.5 it gave the following error:

SQL0119N  An expression starting with "Value" specified in a SELECT 
clause, HAVING clause, or ORDER BY clause is not specified in the 
GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER 
BY clause with a column function and no GROUP BY clause is specified.

Can you kindly point out what is wrong. I do not have much experience with SQL.

Thank you.

What has MySQL to do with the question? (Don't tag products not involved...) — jarlh, Mar 25 '15 at 08:17

score 29 · Answer 1 · edited Nov 15 '19 at 00:42

29

Yes, you can, but you should be consistent regarding the grouping levels. That is, if your query is a GROUP BY query, then in an analytic function you can only use "detail" columns from the "non-analytic" part of your selected columns. Thus, you can use either the GROUP BY columns or the non-analytic aggregates, like this example:

select product_id, company, 
sum(members) as No_of_Members, 
sum(sum(members)) over(partition by company) as TotalMembership 
From Product_Membership 
Group by Product_ID, Company

Hope that helps

SELECT col_1, col_2, sum(Value) over(partition by col_1) as sum_value
    -- also try changing "col_1" to "col_2" in OVER
from myTable
GROUP BY col_2,col_1

edited Nov 15 '19 at 00:42

divega

6,320
1
31
31

answered Mar 25 '15 at 08:24

Dave

967
10
23

I already saw that example. I can do it that way but then I get the total sum for each element in `col_1` instead of the sum for each distinct element in `col_2` per elements in `col_1` – user3557405 Mar 25 '15 at 08:30
I had this same problem, and Dave, your query gave me exactly what I needed. – Sean Branchaw May 06 '16 at 20:19
1

hi, nice example. Does that mean the partitioned column need to be a subset of the grouped by columns? And what does the `sum(sum(` mean? – MJeremy Aug 18 '20 at 08:04

score 8 · Accepted Answer · answered Mar 25 '15 at 08:52

I found the solution.

I do not need to use OVER(PARTITION BY col_1) because it is already in the GROUP BY clause. Thus, the following query gives me the right answer:

SELECT col_1, col_2, sum(Value) as sum_value
from myTable GROUP BY col_1, col_2

since I am already grouping w.r.t col_1 and col_2.

Dave, thanks, I got the idea from your post.

Cannot use group by and over(partition by) in the same query?

2 Answers2

Linked