I have a table like this:
id | Type | Date | DocDate
------------------------------
1 | A | 2014-04-05 | 2014-04-05
2 | A | 2014-05-06 | 2014-05-08
3 | B | 2014-06-06 | 2014-06-06
I need to create a stacked bar char. But to do this I need to transform my table into:
Type | Green | Red
-----------------------
A | 1 | 1
B | 1 | 0
Where Green and Red represent the result of the following condition:
IF (DocDate - Date) <= 1 THEN Green
IF (DocDate - Date) > 1 THEN Red
I tried this query:
SELECT CASE
WHEN (DocDate - Date) <= 1 THEN 'Green'
WHEN (DocDate - Date) > 1 THEN 'Red'
END AS X, Count(OccurrenceID) AS Total
FROM tbloccurrence
GROUP BY CASE
WHEN (DocDate - Date) <= 1 THEN 'Green'
WHEN (DocDate - Date) > 1 THEN 'Red'
END;
But the result is not what I needed. Can anybody help me?
I started by writing a query that pulled each row from your table, and assigned the green or red columns. To do that, you can use the IF()
statement. This takes 3 params: a condition, what to do if that condition is true, and what to do if that condition is false. That gives us this:
SELECT type, IF(DATEDIFF(docdate, dateCol) <= 1, 1, 0) AS green, IF(DATEDIFF(docdate, dateCol) > 1, 1, 0) AS red
FROM myTable;
I also used the DATEDIFF()
function. That returns an integer amount of the days in between the two parameters (it takes DATE or DATETIME params).
Then, I just summed the red and green columns and grouped by type to match your result set:
SELECT type, SUM(IF(DATEDIFF(docdate, dateCol) <= 1, 1, 0)) AS green, SUM(IF(DATEDIFF(docdate, dateCol) > 1, 1, 0)) AS red
FROM myTable
GROUP BY type;
Here is your complimentary SQL Fiddle.