CODEWITHSUNDEEP
postgresql
Richard
Richard

Reputation: 65600

Postgres - is it possible to group by substring of one of my fields?

This is my table:

 id                | integer                 | not null default nextval('frontend_prescription_id_seq'::regclass)
 actual_cost       | double precision        | not null
 chemical_id       | character varying(9)    | not null
 practice_id       | character varying(6)    | not null

I'd like to query results for a particular practice_id, and then sum the actual_cost by date and by the first two characters of the chemical_id. Is this possible in Postgres?

In other words, I'd like the output to look something like this:

processing_date   |    cost    |   chemical_id_substr
01-01-2010             1234        01
01-02-2010             4366        01
01-01-2010             3827        02
01-02-2010             8768        02

This is my current query, but it groups by the whole of chemical_id, not the substring:

    query = "SELECT SUM(actual_cost) as cost, processing_date, "
    query += "chemical_id as id FROM frontend_items"
    query += " WHERE practice_id=%s " 
    query += "GROUP BY processing_date, chemical_id"
    cursor.execute(query, (practice_id,))

I'm not sure how to change this to group by substring, or whether I should add a functional index, or whether I should just denormalise my table and add a new column. Thanks for any help.

Upvotes: 6

Views: 9200

Answers (1)

user330315
user330315

Reputation:

You can do this, but you also need to make sure the substring is used in the select list, not the complete column:

SELECT SUM(actual_cost) as cost, 
       processing_date, 
       left(chemical_id,2) as id --<< use the same expression here as in the GROUP BY
FROM frontend_items
WHERE practice_id= %s 
GROUP BY processing_date, left(chemical_id,2);

Upvotes: 11

Related Questions