joining two tables and getting aggregate data

Question

how do i write a query that returns aggregate sales data for California in the past x months.

 -----------------------        -----------------------
|         order         |      |       customer        |
|-----------------------|      |-----------------------|
| orderId       int     |      | customerId     int    |
| customerId    int     |      | state         varchar |
| deposit       decimal |       -----------------------
| orderDate     date    |   
 ----------------------- 


                  ----------------------- 
                 |      orderItem        |
                 |-----------------------|
                 | orderId      int      |
                 | itemId       int      |
                 | qty          int      |
                 | lineTotal    decimal  |
                 | itemPrice    decimal  |
                  ----------------------- 

Answer 1

Assuming you're looking for the total of qty for each orderItem on all orders with customers located in 'CA' (if you want to sum other fields, it shouldn't be too tough to modify the query):

SELECT
    SUM(oi.qty) as TotalQuantitySold
FROM
    orderItem oi
    INNER JOIN order o
        ON oi.orderId = o.orderId
    INNER JOIN customer c
        ON o.customerId = c.customerId
WHERE
    c.state = 'CA'
    AND DATEDIFF(month, o.orderDate, <some fixed date>)

DATEDIFF is from T-SQL, and calculating that date difference may depend on your platform