Cannot use group by and over(partition by) in the same query?
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
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.