Mysql query to PostgreSQL query

Question

select storeID, itemID, custID, sum(price)
from Sales F
group by storeID, custID, itemID 
    with cube(storeID, custID);

I am going to use this query in postgreSQL but it doesn't work in postgreSQL directly how can I convert this query into postgreSQL query?

If think PostgreSQL's terminology uses ROLLUP instead of CUBE. — Fabian Pijcke
– Fabian Pijcke, Commented Dec 11, 2020 at 10:43
Your MySQL query is not valid: dbfiddle.uk/… What is the expected result — S-Man
– S-Man, Commented Dec 11, 2020 at 10:52

Gordon Linoff · Accepted Answer · 2020-12-11 12:00:06Z

1

Your code will work without the with keyword:

select storeID, itemID, custID, sum(price)
from Sales F
group by itemID, cube(storeID, custID);

I prefer grouping sets for expressing groupings:

select storeID, itemID, custID, sum(price)
from Sales F
group by grouping sets ( (storeID, custID, itemID),
                         (custID, itemID),
                         (storeID, itemID),
                         (itemID)
                       );

If I understand what you want to do, this should be exactly the same.

You could also use cube and then filter:

select storeID, itemID, custID, sum(price)
from Sales F
group by cube(storeID, custID, itemID)
having itemId is not null

answered Dec 11, 2020 at 12:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jim Jones · Accepted Answer · 2020-12-11 10:51:02Z

0

Try this:

select storeID, itemID, custID, sum(price)
from Sales F
group by cube (storeID, itemID, custID)

Check this post for more details on CUBE,GROUPING SETS and ROLLUP.

edited Dec 11, 2020 at 10:51

answered Dec 11, 2020 at 10:44

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Collectives™ on Stack Overflow

Mysql query to PostgreSQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related