Postgres: Group by a LIKE expression?

Question

I'm using Postgres 9.4. I have the following query:

select sum(items) as items, sum(cost) as cost, 
       presentation_code, processing_date as date 
  from vw_presentation_summary 
  where presentation_code LIKE '011%' OR presentation_code LIKE '012%' 
  GROUP BY date, presentation_code;

This gives me results like this:

 items |       cost       | presentation_code |    date
-------+------------------+-------------------+------------
  8243 |        273445.45 | 0212000AAAAACAC   | 2014-03-01
   366 |         10511.99 | 0212000AABBABAB   | 2013-05-01
   461 |          9232.04 | 0212000AABBADAD   | 2014-02-01

But is there any way I can group all the results for 011% together, and all the results for 012% together?

Answer 1

You'd have to replace "presentation_code" in the select and group by elements with an expression such as:

substr(presentation_code,1,3) 

Answer 2

You can group by any expression in postgres, the problem here is that presentation_code needs to be an aggregate of some kind or truncated in a way that it can be grouped on. Here is one example:

select sum(items) as items, sum(cost) as cost, 
       CASE WHEN presentation_code LIKE '011%' THEN '011'
            WHEN presentation_code LIKE '012%' THEN '012'
       END AS code,
       processing_date as date 
  from vw_presentation_summary 
  where presentation_code LIKE '011%' OR presentation_code LIKE '012%' 
  GROUP BY date, code;