CODEWITHSUNDEEP
sqlpostgresqlpivotcrosstab
user11492726
user11492726

Reputation:

SQL query : transform rows to columns

Here's an example of my table.

enter image description here

I need to do a query that shows those IDs who have 0 as a fee on one of two months (11 or 12) or both.

So from the example, I need to show ID 1,3,4 but not 2, like on the screenshot below.

enter image description here

I tried the query below:

SELECT 
    t1.id, t1.month, t1.fee, t2.id, t2.month, t2.fee
FROM
    table t1, table t2
WHERE t1.id = t2.id
  AND t1.month = '11'
  AND t2.month = '12'
  AND (t1.fee = 0 OR t2.fee = 0);

But with this query, I only see ID 1,3 but not ID 4. I guess it's because of t1.id = t2.id but no idea how to do otherwise.

Upvotes: 1

Views: 134

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use conditional aggregation. In Postgres, this can make use of the filter syntax:

SELECT t.id,
       11 as month,
       MAX(t.fee) FILTER (WHERE t.month = 11) as fee_11, 
       12 as month,
       MAX(t.fee) FILTER (WHERE t.month = 12) as fee_12
FROM t
GROUP BY t.id
HAVING MAX(t.fee) FILTER (WHERE t.month = 11) = 0 OR
       MAX(t.fee) FILTER (WHERE t.month = 12) = 0;

Note: The two month columns are redundant.

Upvotes: 3

nicolasL
nicolasL

Reputation: 136

Sql ansi compliant query

    SELECT id, 
        MAX(CASE WHEN MONTH = 11 THEN MONTH ELSE NULL END) AS month11, 
        MAX(CASE WHEN MONTH = 11 THEN fee ELSE NULL END) AS fee11, 
        MAX(CASE WHEN MONTH = 12 THEN MONTH ELSE NULL END) AS month12, 
        MAX(CASE WHEN MONTH = 12 THEN fee ELSE NULL END ) AS fee12
    FROM t
    GROUP BY id
    HAVING ( MAX(CASE WHEN MONTH = 11 THEN fee ELSE NULL END) = 0 OR MAX(CASE WHEN MONTH = 12 THEN fee ELSE NULL END ) = 0 )
    ORDER BY id

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you need conditional aggregation

  select id,month,max(case when month=11 then fee end) fee11,
     max(case when month=12 then fee end) as fee12
    from (
    select * from table t1
    where t1.id in ( select id from table where fee=0)
    ) a group by id,month

Upvotes: 2

Related Questions