SQL with grouping sets returns the initial table-CodePudding

I have a sample table like this:

name	manager	country	position	salary
Mike	Mark	USA	Content Writer	40000
Kate	Mark	France	SEO Specialist	12000
John	Caroline	USA	Outreach Expert	32000
Alice	Caroline	Italy	SEO Specialist	50000
Philip	Caroline	Italy	Marketing Manager	30000
Julia	Caroline	Italy	SEO Specialist	44000

I'm writing a query to get the avg. salary from the table grouped by different columns:

SELECT
    name,
    manager,
    country,
    position,
    AVG(salary)
FROM
    table
GROUP BY GROUPING SETS
    (manager),
    (name, country),
    (position),
    ()

However, the output is basically the same table I had in the beginning, just in a different order. Why is that? How do I fix this query to return the grouping I need?

CodePudding user response：

As in the examples in the documentation, I believe you need to wrap the sets in brackets. e.g.

SELECT
    name,
    manager,
    country,
    position,
    AVG(salary)
FROM
    table
GROUP BY GROUPING SETS
    ( -- Added this bracket to OP
    (manager),
    (name, country),
    (position),
    ()
    ) -- Added this bracket to OP