1 | initial version |
There are different ways to divide columns in MySQL based on certain criteria, depending on the specific task you want to accomplish. Below are some examples:
SELECT id, amount,
CASE
WHEN amount >= 1000 THEN 'high'
ELSE 'low'
END AS category
FROM mytable;
This query will return three columns: "id," "amount," and "category," where the "category" column represents the value "high" if the "amount" value is >= 1000, and "low" otherwise.
SELECT id, amount,
IF(amount >= 1000, 'high',
IF(amount >= 500, 'medium', 'low')
) AS category
FROM mytable;
This query will divide the "amount" column into three categories: "high" if the value is >= 1000, "medium" if it's >= 500 but < 1000, and "low" otherwise.
SELECT category, COUNT(*) AS count, AVG(amount) AS avg_amount
FROM (
SELECT id, amount,
CASE
WHEN amount >= 1000 THEN 'high'
ELSE 'low'
END AS category
FROM mytable
) AS subquery
GROUP BY category;
This query will divide the "amount" column into two categories as before, but additionally, it will count how many rows belong to each category and calculate the average "amount" value for each category. The result will be something like:
category | count | avg_amount
---------+-------+------------
high | 10 | 1500.00
low | 30 | 250.00