CODEWITHSUNDEEP
amazon-redshift
RustyShackleford
RustyShackleford

Reputation: 3667

How to multiple decimal numbers in column within a group by?

I have sql table that looks like this:

date                  id       value     type
2020-01-01            1        1.03      a
2020-01-01            1        1.02      a
2020-01-02            2        1.06      a
2020-01-02            2        1.2       a
2020-01-03            3        1.09      b

I need to build a query that groups by date,id, and type by multiplying the value column whereever type = 'a'.

what new table should look like:

date                  id       value      type
2020-01-01            1        1.0506      a
2020-01-02            2        1.272       a
2020-01-03            3        1.09        b

currently I am building this query,

select 
date, id, value, type
from my_table
where date between 'some date' and 'some date'

and trying to fit in EXP(SUM(LOG(value)

but, how do I do the multiplication only where type = 'a' in a group by?

edit:

  1. there are more than 2 values in the type column
  2. I am using redshift. Not postgresql.

Upvotes: 0

Views: 148

Answers (1)

Paulo Pereira
Paulo Pereira

Reputation: 1115

select date
     , id
     -- use the 'case' syntax to check if it is type 'a'
     , case when type = 'a' then EXP(SUM(LOG(value::float))) -- your multiply logic
            else max(value) -- use min or max to pick only one value
       end as value
  from my_table
 where date between 'some date' and 'some date'
 group
    by date, id, type

Upvotes: 1

Related Questions