CODEWITHSUNDEEP
sqlsql-serverselectgrouping-sets
Rafael Perbeline
Rafael Perbeline

Reputation: 7

"Each GROUP BY expression must contain at least one column that is not an outer reference" with "grouping set"

I have the following SQL query, my goal was to make a subtotal and a general tota, grouping the subtotals by products, but my problem would be that if I use only the "group by" he would have to add all the fields of the "select", and the result would not be the desired one. So I tried to use the "grouping set", but it gives the following error

"Each GROUP BY expression must contain at least one column that is not an outer reference"

Would anyone have a solution either for the above problem or to be able to group only by product?

SELECT 
    p.id_product,
    p.name,
    dd.name ,
    isnull(p.internal code, '------'),
    {fn CONCAT(CAST(d.id_division AS varchar), {fn CONCAT(' - ', d.name)}) },
    sum(vs.value1),
    sum(p.value2),
    sum(p.value2 * vs.value1)
FROM product p
    LEFT JOIN division d on d.id_division  = p.id_division
    LEFT JOIN division2 dd on dd.id_product = p.id_product
    LEFT JOIN value_stockroom vs on vs.id_product = p.id_product and vs.id_division2 = dd.id_division2
GROUP BY
    GROUPING SETS((p.id_product, p.name), (dd.name), (p.internal_code), (d.id_division, d.name), NULL)
ORDER BY 
    p.name, p.id_product, d.name

I'm new using the Grouping set clause

Upvotes: 0

Views: 258

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

NULL is not allowed in GROUP BY because it is a constant. You want empty parentheses:

GROUP BY GROUPING SETS( (p.id_product, p.name), 
                        (dd.name),
                        (p.internal_code),
                        (d.id_division, d.name),
                        ()
                      )

Upvotes: 0

Related Questions