CODEWITHSUNDEEP
mysqlsqldatabaseselectgroup-by
Елисей Горьков
Елисей Горьков

Reputation: 447

Could I use case statement not in query but ONLY in group by()?

Can I use case statement only in Group by()?

For example, I need to group results of my calculation by groups of incom level.

user_id   income
1          10
2          20
3          100
4          500

I want to get result:

 number_of_users  income
  1               <10
  2               10 - 150
  1               >150

Could I use case statement not in query but ONLY in group by()?

Upvotes: 1

Views: 40

Answers (2)

jarlh
jarlh

Reputation: 44766

Modified version of @Lukasz' answer.

select income, COUNT(*) AS num_of_users
from
(
    SELECT CASE WHEN income < 10 THEN '<10'
                WHEN income <= 150 THEN '10-150'
                ELSE '> 150'
           END AS income
    FROM tab
) dt
group by income

Using a derived table to

  • Save some typing
  • Avoid silly errors caused by different case expressions in the select list and the group by clause
  • Be ANSI SQL compliant.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175636

You could use CASE:

SELECT CASE WHEN income < 10 THEN '<10'
              WHEN income <= 150 THEN '10-150'
              ELSE '> 150'
         END AS income, COUNT(*) AS num_of_users
FROM tab
GROUP BY CASE WHEN income < 10 THEN '<10'
              WHEN income <= 150 THEN '10-150'
              ELSE '> 150'
         END

Upvotes: 2

Related Questions