Menu
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..
220
If mixed-case letters or special symbols, or spaces are required, quotes must be used. Column aliases can be used for derived columns. Column aliases can be used with GROUP BY and ORDER BY clauses. We cannot use a column alias with WHERE and HAVING clauses.
The case statement in SQL returns a value on a specified condition. We can use a Case statement in select queries along with Where, Order By, and Group By clause. It can be used in the Insert statement as well.
And the answer is yes it does still apply.
Yes, you can certainly use column aliases in your "order by" clause. You can verify it works with the built-in mySql "user" table: select User as name,Host from user order by name; If it "errored out", then something else must have been wrong with your query.
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.
139
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
