sql combining two queries and truncating date

I am trying to truncate dates to only get year/month as opposed to the form they are in which is year/month/day.time

What I want to do is count all of the cars that sold each month and all of the suvs that sold each month, having something like:

// counts cars    
select SellDate, count(*) 
from category 
where machineIdentification = 1 
GROUP BY SellDate  

// counts suv's  
select SellDate, count(*) 
from category 
where machineIdentification = 2 
GROUP BY SellDate  

Separately running each query gives me a list of dates (y/m/d/time to the second)开发者_如何学编程 and the number 1 because only 1 car or suv sold that exact time, however I am trying to group by SellDate and truncate the date so it only shows me the total numbers each month.

what I am trying to do is combine the queries and end up with values like so:

2009-01 23 10  
2009-02 13 14  
2009-03 29 7  

With the first column being the year.month, the second being # of cars sold and the third being # of suv's sold

select
   date(SellDate, 'start of month'),
   SUM (CASE machineIdentification = 1 THEN 1 ELSE 0 END) AS carcount ,
   SUM (CASE machineIdentification = 2 THEN 1 ELSE 0 END) AS suvcount 
from category 
where machineIdentification IN (1, 2 )
GROUP BY date(SellDate, 'start of month')

I'd concatenate the year/month in the client code so you can have "July 2010" for example

(I can't test in SQLLite, sorry, but this should be close except for output year/month format)

It could involve a union in a subquery and a format on the date, reformatted for SQLLite:

SELECT SellDate, COUNT(CARS) AS Cars, COUNT(SUVS) AS SUVS
FROM         
 (SELECT STRFTIME('%Y-%m',SellDate) AS SellDate,
         MachineIdentification AS CARS, null AS SUVS
     FROM          Category
     where machineIdentification = 1 
     UNION
     SELECT STRFTIME('%Y-%m',SellDate) AS SellDate, 
         null AS CARS, MachineIdentification AS SUVS
     FROM Category
     where machineIdentification = 2 
  )
GROUP BY SellDate