CODEWITHSUNDEEP
sqlpostgresql
Wannisa Cheamsiri
Wannisa Cheamsiri

Reputation: 1

How to group by only one column?

I would like to select only one column (Failed_operation) and distinct column (SN) with hide column as below code but I got error ERROR: column "rw_pcba.sn" must appear in the GROUP BY clause or be used in an aggregate function

I tried remove distinct on (SN) then the result was appear but result are including duplicate SN too. I don't want duplicate SN in result.

SELECT DISTINCT ON (sn) Failed_operation
    ,count(CASE WHEN (extract(day FROM NOW() - fail_timestamp)) > 0
                AND (extract(day FROM NOW() - fail_timestamp)) <= 15 THEN 1 ELSE NULL END) AS AgingLessThan15
    ,count(CASE WHEN (extract(day FROM NOW() - fail_timestamp)) > 15
                AND (extract(day FROM NOW() - fail_timestamp)) <= 30 THEN 1 ELSE NULL END) AS Aging16To30
    ,count(CASE WHEN (extract(day FROM NOW() - fail_timestamp)) > 30
                AND (extract(day FROM NOW() - fail_timestamp)) <= 60 THEN 1 ELSE NULL END) AS Aging31To60
    ,count(CASE WHEN (extract(day FROM NOW() - fail_timestamp)) > 60 THEN 1 ELSE NULL END) AS AgingGreaterThan60
    ,count(CASE WHEN (extract(day FROM NOW() - fail_timestamp)) <= 0 THEN 1 ELSE NULL END) AS Aging0
FROM rw_pcba
WHERE rework_status = 'In-Process'
GROUP BY Failed_operation
ORDER BY sn
    ,Failed_operation ASC

Upvotes: 0

Views: 69

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You want to aggregate by sn as well as failed_operation. I also think you can simplify the calculation of each column:

SELECT sn, Failed_operation,
       count(*) filter (where fail_timestamp > current_date and fail_timestamp < current_date + interval '15 day') as AgingLessThan15,
       count(*) filter (where fail_timestamp > current_date + interval '15 day' and fail_timestamp < current_date + interval '30 day') as Aging16To30,
       count(*) filter (where fail_timestamp > current_date + interval '30 day' and fail_timestamp < current_date + interval '600 day') as Aging31To60,
       count(*) filter (where fail_timestamp > current_date + interval '60 day') as AgingGreaterThan60,
       count(*) filter (where fail_timestamp <= current_date) as Aging0
FROM rw_pcba
WHERE rework_status = 'In-Process'
GROUP BY sn, Failed_operation
ORDER BY sn, Failed_operation ASC;

I prefer direct date comparisons for this type of logic rather than working with the difference between the dates. I simply find it easier to follow. For instance, using current_date rather than now() removes the question of what happens to the time component of now().

EDIT:

In older versions of Postgres, you can phrase this using sum:

       sum( (fail_timestamp > current_date and fail_timestamp < current_date + interval '15 day')::int ) as AgingLessThan15,

Upvotes: 0

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

You need to group by using the column sn, when you are using group by then it would be distinct combination of sn and failed_operation you don't have to specify distinct.

SELECT sn, Failed_operation,
count  (case when (extract(day from NOW() - fail_timestamp)) >0 and (extract(day from NOW() - fail_timestamp))<=15 then 1 else null end) as AgingLessThan15,
count  (case when (extract(day from NOW() - fail_timestamp)) >15 and (extract(day from NOW() - fail_timestamp))<=30 then 1 else null end) as Aging16To30,
count  (case when (extract(day from NOW() - fail_timestamp)) >30 and (extract(day from NOW() - fail_timestamp))<=60 then 1 else null end) as Aging31To60,
count  (case when (extract(day from NOW() - fail_timestamp)) >60 then 1 else null end) as AgingGreaterThan60,
count  (case when (extract(day from NOW() - fail_timestamp)) <=0 then 1 else null end) as Aging0
FROM rw_pcba where rework_status='In-Process' 
GROUP by sn,Failed_operation  ORDER BY  sn,Failed_operation ASC

Upvotes: 1

Related Questions