Cannot use group by and over(partition by) in the same query?
Solution 1
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.
Solution 2
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
Related videos on Youtube
user3557405
Updated on November 15, 2020Comments
-
user3557405 over 3 years
I have a table
myTable
with 3 columns.col_1
is anINTEGER
and the other 2 columns areDOUBLE
. For example,col_1={1, 2}, col_2={0.1, 0.2, 0.3}
. Each element incol_1
is composed of all the values ofcol_2
andcol_2
has repeated values for each element incol_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 theValue
column partition bycol_1
and grouped bycol_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.
-
jarlh about 9 yearsWhat has MySQL to do with the question? (Don't tag products not involved...)
-
-
user3557405 about 9 yearsI 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 incol_2
per elements incol_1
-
Sean Branchaw about 8 yearsI had this same problem, and Dave, your query gave me exactly what I needed.
-
MJeremy almost 4 yearshi, 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?