Ordered Analytical Functions not allowed in GROUP BY Clause

Question

I have a query joining 2 tables with a SELECT statement for fields in both tables, and an Ordered Analytical Function to calculate the total volume for specific customers. I'm getting the error "Ordered Analytical Functions not allowed in GROUP BY Clause" when I try to group the fields. I need the GROUP BY because there are other fields that need to be grouped but I also need the SUM() OVER (PARTITION BY()) for other calculations. How can I create the subquery so as to get rid of the error?

The query is something like this:

a.cust_no,
b.cust_name,
a.location,
c.product,
SUM(a.volume),
SUM(a.weight),
SUM(volume) OVER (PARTITION BY cust_no ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS fixed_volume, 
SUM(CASE WHEN a_flag = 'Y' THEN volume ELSE 0 END)  OVER (PARTITION BY cust_no ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS bias_volume
FROM test_table a
JOIN test_table2 b ON a.cust_no = b.cust_no
JOIN test_table3 c ON a.cust_no = c.cust_no
GROUP BY 1,2,3,4

Does this answer your question? [Can I group by in SQL query with window function?](https://stackoverflow.com/questions/40594466/can-i-group-by-in-sql-query-with-window-function) — nbk, Sep 16 '21 at 22:34

dnoeth · Accepted Answer · 2021-09-17T07:44:50.873

1

There's no OLAP function in your GROUP BY clause.

I would expect a 3504 Selected non-aggregate values must be part of the associated group, this should fix it:

select
    a.cust_no,
    b.cust_name,
    a.location,
    c.product,
    SUM(a.volume),
    SUM(a.weight),
    --volume is a detail row -> doesn't exist after aggregation
    --SUM(a.volume) OVER (PARTITION BY cust_no) AS fixed_volume, 
    --aggregated volume can be used
    SUM(SUM(a.volume)) OVER (PARTITION BY cust_no) AS fixed_volume, 
    --same logic
    SUM(SUM(CASE WHEN a_flag = 'Y' THEN a.volume ELSE 0 END))  OVER (PARTITION BY cust_no ) AS bias_volume
FROM test_table a
JOIN test_table2 b ON a.cust_no = b.cust_no
JOIN test_table3 c ON a.cust_no = c.cust_no
GROUP BY 1,2,3,4

edited Sep 17 '21 at 07:44

answered Sep 16 '21 at 22:23

dnoeth

59,503
4
39
56

Appreciate your help. It did solve my problem but it'll be great if you could please help me in understanding why it worked. – Ankita Sep 17 '21 at 00:34
1

OLAP-functions are calculated *after* GROUP BY/HAVING -> only aggregated columns or columns GROUP BY can be used. – dnoeth Sep 17 '21 at 07:41

Ordered Analytical Functions not allowed in GROUP BY Clause

1 Answers1