Help With Query to Get Totals Sorted by Month & Year

[Nicodemas]

I am trying to figure out a query. I believe it is possible, but my SQL knowledge has been waning in the past year due to disuse. Anyone wish to help?

I have three tables.

Table: Fruits

FruitID  |  FruitName
-----------------------
1           Orange
2           Bananas

Table: Customers

CustomerID  |  CustomerName
----------------------------
1              Bill
2              Jim

Table: CustomerOrders

OrderID  |  FruitType  |  QuantityBought  |  CustomerID  |  DateBought
-------------------------------------------------------------------------
1            1             15                1             2010-04-02
1            1             90                1             2010-04-04
1            1             31                1             2010-06-06
1            1             26                1             2011-06-02
1            1             16                1             2011-07-04
1            1             40                1             2011-08-02
1            2             15                1             2010-04-02
1            2             90                1             2010-05-04
1            2             31                1             2010-06-06
1            2             26                1             2011-06-02
1            2             16                1             2011-07-04
1            2             40                1             2011-08-02

My goal is to get a query that would return a result like such:

Results:

FruitID  | Customer  | QuantityBought  | MonthBought  |  YearBought
------------------------------------------------------------------------
1          Bill             105                  4            2010
1          Bill             31                   6            2010

So far, I have come up with this, but it isn't right (else I wouldn't be here!).

SELECT FruitID, CustomerName, SUM(QuantityBought), MONTH(DateBought), YEAR(DateBought) From CustomerOrders, Customers WHERE Customers.CustomerID = CustomerOrders.CustomerID AND YEAR(DateBought) >= 2010

[gpl]

Try this, the group by will order the results, Im not sure how you want to order the results from your example

SELECT 
FruitID, 
CustomerName, 
SUM(QuantityBought), 
MONTH(DateBought), 
YEAR(DateBought) 
From CustomerOrders inner join Customers on Customers.CustomerID = CustomerOrders.CustomerID 
WHERE YEAR(DateBought) >= 2010
GROUP BY 
FruitID, 
CustomerName, 
YEAR(DateBought), 
MONTH(DateBought)