Lets say we have a query
SELECT recordType,SUM(amount) FROM records GROUP BY recordType
Output
1: 50
2: 100
3: 150
4: 200
We have 4 different record types which are 1,2,3 and 4. The above query will obviously return values grouped for those 4 types. How can I adjust the query to show grouping based on their paired grouping. Like return the result of recordType 1 and recordType 2 in one row, and for 3 and 4 in second row.
Expected Output
Group 1: 150
Group 2: 350
Like return the result of recordType 1 and recordType 2 in one row, and for 3 and 4 in second row.
You could do this with a case statement. It won't perform very well, but it'll work. Depending on your actual values, the final query could look something like this:
group by case
when type in(1, 2) then 1
when type in(3, 4) then 2
when ...
else ...
end
Part of the problem is doing the grouping correctly, the other part is getting the names of the groups.
If you really have the numbers in question, you can do:
select concat('Group ', cast((recordType - 1)/2 as int)), count(*)
from records r
group by cast((recordType - 1)/2 as int)
If the values are actually not so arithmetically amenable, then a variable is possibly the simplest method:
select concat('Group ', @rn := @rn + 1), count(*)
from records r cross join (select @rn := 0) const
group by (case when type in (1, 2) then 1
when type in (3, 4) then 2
end)
