Coarsening GROUP BY in SQL

Question

Given a table of the following form

CustumerID | Amount
-------------------
1          | 100 
1          | 50
2          | 30
3          | 40
4          | 50

the SQL query

SELECT  SUM(Amount)
GROUP BY Customer ID

will return

CustomerID | Amount
-------------------
1          | 150
2          | 30
3          | 40
4          | 50

Is there a way to 'coarsen' the GROUP BY statement such that CustomerIDs 1 and 2 and CustomerIDs 3 and 4 are grouped together, i.e. that a result like

CustomerID   | Amount
---------------------
1,2          | 180
3,4          | 90

is returned?

Actually, I want to group by a partition, say, 1..5,6,7,8,9..11,12..14 etc.. I just gave an example which I considered to be minimal (but apparently isn't, as there are shortcuts for this available, cf. Florin's answer.) — Hans, Mar 22 '12 at 08:55
So... arbitrary-sized groups, determined prior to coding? If so, turbanoff's answer is the way to go. — , Mar 22 '12 at 08:57
If it is something fixed add a grouping column to customer table, otherwise create a grouping table with view criteria (i.e. if different divisions of a company need to see differently grouped customers create a table (DivisionID, CustomerID, GroupCode). — Nikola Markovinović, Mar 22 '12 at 09:00

Florin Ghita · Answer 1 · 2012-03-22T08:59:47.590

2

Sure, depends on how you want to group them.

In MySQL:

select group_concat(customerid) as customers, sum(amount) as amount
from your_table
group by floor((customerid+1)/2) --or another function that returns a unique value for the customers in the group

In Oracle 11g:

select list_agg(customerid, ',') as customers, sum(amount) as amount
from your_table
group by trunc((customerid+1)/2) --or another function that returns a unique value for the customers in the group

edited Mar 22 '12 at 08:59

answered Mar 22 '12 at 08:43

Florin Ghita

17,525
6
57
76

Thank you for your answer. However, I may have oversimplified the actual problem too much. What if I want to group the customerIDs in a partition 1..4,5,6,7,8,9..11,12..14? – Hans Mar 22 '12 at 08:48
I don't understand. What means a partition? how many groups do you have? Update your question to understand the real case. – Florin Ghita Mar 22 '12 at 08:54
actually it should be trunc((customerid+1)/2) – arturro Mar 22 '12 at 08:56

score 2 · Accepted Answer · answered Mar 22 '12 at 08:45

select case when CustomerID=1 or CustomerID=2 then '1,2'
            when CustomerID=3 or CustomerID=4 then '3,4'
                                              else CustomerID
       end
     , sum(amout)
from TABLE
group by case when CustomerID=1 or CustomerID=2 then '1,2'
              when CustomerID=3 or CustomerID=4 then '3,4'
                                                else CustomerID
         end

score 0 · Answer 3 · edited May 23 '17 at 10:34

If you don't want to create a table, I guess you should at least create a VIEW if you want to make an arbitrary group by of your customers

Here is a SQL SERVER 2005 and later solution, based on a very interesting answer https://stackoverflow.com/a/273330/1236044

create table TABLETEST ([CustomerID] INT, [AMOUNT] INT)
go
insert into TABLETEST values (1,100)
insert into TABLETEST values (1,50)
insert into TABLETEST values (2,30)
insert into TABLETEST values (3,40)
insert into TABLETEST values (4,50)
go

create view VIEWTESTCAT as
    select TABLETEST.*,'category1' as [Category] from TABLETEST where CustomerID in (1,2)
    union
    select TABLETEST.*,'category2' as [Category] from TABLETEST where CustomerID in (3,4)
go

Select [NameValues], SUM([Amount]) from
(
  SELECT  
    STUFF((SELECT distinct ',' + convert(nvarchar(max),VIEWTESTCAT.[CustomerID])  FROM VIEWTESTCAT
      where VIEWTESTCAT.Category = VIEWTESTCATALIAS.Category
      order by ',' + convert(nvarchar(max),VIEWTESTCAT.[CustomerID])
      FOR XML PATH ('')
     ),1,1,'') AS NameValues
    , Amount
  FROM VIEWTESTCAT VIEWTESTCATALIAS
) as TABLETESTAUX
group by NameValues

drop view VIEWTESTCAT
go

drop table TABLETEST
go

Coarsening GROUP BY in SQL

3 Answers3