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
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),
()
)