Home > Back-end >  SQL with grouping sets returns the initial table
SQL with grouping sets returns the initial table

Time:09-16

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
  • Related