CODEWITHSUNDEEP
mysqlsqlgroup-by
user1783504
user1783504

Reputation: 453

Creating a summary report by group and quarter SQL

I need to create a summary report of a transaction dataset with SQL in SAS

My dataset:

   -ID  Type    TAG QTR Amt
   -1234    WX  D   1   40
   -1234    WX  D   1   10
   -1234    WX  D   1   40
   -1234    WX  D   2   30
   -1234    WX  D   2   50
   -4444    I   G   1   80
   -4444    I   G   1   20
   -4444    I   G   1   50
   -4444    I   G   1   70
   -4444    I   G   1   30
   -4444    I   G   2   40
   -4444    I   G   2   20

I would like to get a summary report by ID and quarter like the below:

    -ID    Type    TAG     QTR1     QTR2
   -1234    WX      D       90      80
   -4444    I       G       250     60

I can't manage to get the sum by quarters in different columns.

Thanks for the help.

Upvotes: 0

Views: 93

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13248

select      id
            , type
            , tag
            , sum(case when qtr = 1 then amt else 0 end) as qtr1
            , sum(case when qtr = 2 then amt else 0 end) as qtr2
from        tbl
group by    id
            , type
            , tag

Upvotes: 1

Related Questions