I have a SELECT statement being calculated from a CASE WHEN THEN state (or could use multiple IF statements) aliased as 'Length', and I need to correctly GROUP the results together. The SELECT seems to be working, but the group groups them wrong. Here is my statement:
SELECT CASE
WHEN DATEDIFF(o.EndDate, o.StartDate) < 30 THEN '<1 Month'
WHEN DATEDIFF(o.EndDate, o.StartDate) < 90 THEN '1 - 2 Months'
WHEN DATEDIFF(o.EndDate, o.StartDate) < 210 THEN '3 - 4 Months'
ELSE '>4 Months' END AS 'Length',
COUNT(DISTINCT(person.ID)) AS 'COUNT'
FROM person
INNER JOIN opportunity AS o
INNER JOIN Organization AS org
ON person.EntityID = o.id
AND O.OrganizationID = Org.ID
WHERE person.TitleID = 2
AND o.bID = 1
GROUP BY 'Length'
ORDER BY 'Length' ASC;
This groups all results into '3 - 4 Months' which isn't right..
You need to use the whole CASE
statement in the GROUP BY
clause if you don't wrapped it in a subquery.
SELECT CASE
WHEN DATEDIFF(o.EndDate, o.StartDate) < 30 THEN '<1 Month'
WHEN DATEDIFF(o.EndDate, o.StartDate) < 90 THEN '1 - 2 Months'
WHEN DATEDIFF(o.EndDate, o.StartDate) < 210 THEN '3 - 4 Months'
ELSE '>4 Months'
END AS `Length`,
COUNT(DISTINCT(person.ID)) AS `COUNT`
FROM person
INNER JOIN opportunity AS o
ON person.EntityID = o.id
INNER JOIN Organization AS org
ON o.OrganizationID = Org.ID
WHERE person.TitleID = 2
AND o.bID = 1
GROUP BY CASE
WHEN DATEDIFF(o.EndDate, o.StartDate) < 30 THEN '<1 Month'
WHEN DATEDIFF(o.EndDate, o.StartDate) < 90 THEN '1 - 2 Months'
WHEN DATEDIFF(o.EndDate, o.StartDate) < 210 THEN '3 - 4 Months'
ELSE '>4 Months'
END
ORDER BY Length ASC;
Remove also the single quotes around the column name in the ORDER BY
clause.