获取马盖托各城市的终身销售 [英] Get lifetime sales by city in Magento

问题描述

我第一次面对巨大的Magento数据库.对于Magento来说，所有内容都包含一个以上的表:sales_X，其中X可以采用6个值，呵呵.我正式迷路了.我希望能够从Magento记录中获取以下信息，但直言不讳，我不知道从哪里开始.

For the first time I'm facing the gigantic Magento database. For everything Magento has more that one table: sales_X where X can take 6 values, hehe. I'm officially lost. I want to be able to obtain the following info out of Magento records but to be blunt, I don't know where to start.

这是我需要提取的内容(对于加利福尼亚州):

This is is what I need to extract (For California state):

City | County | Tax Rate | Tax Dollar Amount Collected | Total City Sales Amount

Example: Santa Monica | Los Angeles | 9.5% | $95.00 | $1,000

如果有人可以帮助我，我将非常感激.如果可以使用管理网格来实现这一点，则指向该方向也可以.

If someone can help me with this I will appreciate very much. If it's possible to achieve this using admin grids, pointing in that direction works too.

推荐答案

我想您的帐单地址需要此地址:

I presume you need this for the billing address:

SELECT `city`, `region`, `tax_percent`, SUM(`tax_amount`), SUM(`grand_total`) 
FROM `sales_flat_order_address` 
LEFT JOIN `sales_flat_order` ON `sales_flat_order_address`.parent_id = `sales_flat_order`.entity_id 
WHERE `address_type` = 'billing' 
GROUP BY `tax_percent`, `city`

如果要根据送货地址进行订购:

If you want it based on shipping address:

SELECT `city`, `region`, `tax_percent`, SUM(`tax_amount`), SUM(`grand_total`) 
FROM `sales_flat_order_address` 
LEFT JOIN `sales_flat_order` ON `sales_flat_order_address`.parent_id = `sales_flat_order`.entity_id 
WHERE `address_type` = 'shipping' 
GROUP BY `tax_percent`, `city`

这篇关于获取马盖托各城市的终身销售的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！