MySQL Query Rollup-CodePudding

I have a problem. My MySQL query doesn't work. How could I fix this ? my query

select state, city, sum((sales.retail_price - products.wholesale_price) * sales.quantity) as profit 
from products, sales
where sales.product_id = products.product_id
group by rollup (state, city)
order by state, city;

my error

11:39:12    select state, city, sum((sales.retail_price - products.wholesale_price) * sales.quantity) as profit  from products, sales where sales.product_id = products.product_id group by rollup (state, city) order by state, city   Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(state, city) order by state, city' at line 4  0.000 sec

my schema

-- Create some tables and insert some rows.
create table products (product_id integer, wholesale_price real);
insert into products (product_id, wholesale_price) values 
    (1, 1.00),
    (2, 2.00);

create table sales (product_id integer, retail_price real, 
    quantity integer, city varchar, state varchar);
insert into sales (product_id, retail_price, quantity, city, state) values 
    (1, 2.00,  1, 'SF', 'CA'),
    (1, 2.00,  2, 'SJ', 'CA'),
    (2, 5.00,  4, 'SF', 'CA'),
    (2, 5.00,  8, 'SJ', 'CA'),
    (2, 5.00, 16, 'Miami', 'FL'),
    (2, 5.00, 32, 'Orlando', 'FL'),
    (2, 5.00, 64, 'SJ', 'PR');

CodePudding user response：

Try this:

select state, city, sum((sales.retail_price - products.wholesale_price) * 
sales.quantity) as profit 
from products, sales
where sales.product_id = products.product_id
group by state, city WITH ROLLUP
order by state, city;

You can see here the ROLL UP behaviour