MySQL查询汇总

Question

我有个问题。我的MySQL查询不起作用。我怎么才能解决这个问题？我的疑问

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;

我的错误

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

我的模式

-- 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');

Answer 1

试试这个：

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;

你可以看到这里的卷起行为