Count of Orders By City

My Query:

select  distinct a.city, 
        (
            select count(*) 
            from sales_flat_order 
            where a.email=b.customer_email 
            group by a.city
  开发者_Go百科      ) as 'lora' 
from sales_flat_order_address a, sales_flat_order b 
group by a.city

Result:

City1 : 15
City2: (NULL)

Expected Result:

City1 : 13
City2: 2

---

select a.city, count(*) as 'lora' 
from sales_flat_order_address a
        INNER JOIN sales_flat_order b 
            ON a.email=b.customer_email 
group by a.city

---

SELECT count(sfoa.city) as count_of_city, sfoa.city 
FROM sales_flat_order as sfo 
LEFT JOIN sales_flat_order_address as sfoa ON sfo.entity_id=sfoa.parent_id 
WHERE sfoa.address_type="shipping" 
GROUP BY sfoa.city;

Is that what you are looking for (designed for magento 1.5)?

EDIT Needed to have a filter for address type as every order will have a billing and shipping address.

---

select count(*) from sales_flat_order where a.email=b.customer_email group by a.city

can return multiple rows. it can not be used in a SELECT clause as query field, unless EMAIL field has some unique index e.g. besides, it contains syntax error from a point of view of ANSI SQL. GROUP BY clause is redundand here

---

seems problem in no where clause for binding sales_flat_order_address a and sales_flat_order b