date manipulation

[dbosz]

I have a table (as below)

date                Type

2003-01-01 A
2003-02-23 C
2003-03-12 C
2003-02-04 B
2003-01-02 B

date format is date and type is text.

How can i get date like this

 Month             A     B    C      Total
Januari             1    1     0         2 
Februari           0    1     1         2 
March               0    0     1         1
 Total               1    2     2         5

Anybody have the solution?
Please help me

[nnichols]

SELECT DATE_FORMAT(date,'%M'),
SUM(if(Type = 'A', 1, 0)) AS A,
SUM(if(Type = 'B', 1, 0)) AS B,
SUM(if(Type = 'C', 1, 0)) AS C,
SUM(if(Type IS NOT NULL, 1, 0)) AS Total
FROM dates
GROUP BY DATE_FORMAT(date, '%Y%m')
ORDER BY date

This will not give the column totals but they are easily done in your script.

Hope this helps 😉