1

This is my approach:

select distinct (invoice_no) as no,sum(total),
                sum(case when department_id=2 then total end) as a2,
                sum(case when department_id=3 then total end) as a3,
                sum(case when department_id=4 then total end) as a4,
                sum(case when department_id=5 then total end) as a5,
                sum(case when department_id=6 then total end) as a6
from article_sale
where  invoice_date = '2018-10-01' group by no order by no ASC

The query returns output like this:

no      sum a2      a3    a4    a5      a6
68630   690 NULL    75    404   NULL    210.8
68631   0   NULL    NULL  NULL  NULL    NULL
68632   132 NULL    45    87    NULL    NULL
68633   75  NULL    75    NULL  NULL    NULL
68634   523 NULL    130   NULL  NULL    392.55
68635   0   NULL    NULL  NULL  NULL    NULL
68636   310 NULL    NULL  218   NULL    91.91
68637   273 NULL    NULL  NULL  NULL    273.24
68638   0   NULL    NULL  NULL  NULL    NULL

I only want to get rows where a6 is NOT NULL. Other rows shall be filtered.
Desired output:

no      sum a2      a3    a4    a5      a6
68630   690 NULL    75    404   NULL    210.8
68634   523 NULL    130   NULL  NULL    392.55
68636   310 NULL    NULL  218   NULL    91.91
68637   273 NULL    NULL  NULL  NULL    273.24

How to best achieve this?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Muhammad Iqbal
  • 1,394
  • 1
  • 14
  • 34

3 Answers3

3

Add a HAVING clause:

SELECT invoice_no                                  AS no
     , sum(total)                                  AS sum_total
     , sum(total) FILTER (WHERE department_id = 2) AS a2
     , sum(total) FILTER (WHERE department_id = 3) AS a3
     , sum(total) FILTER (WHERE department_id = 4) AS a4
     , sum(total) FILTER (WHERE department_id = 5) AS a5
     , sum(total) FILTER (WHERE department_id = 6) AS a6
FROM   article_sale
WHERE  invoice_date = '2018-10-01'
GROUP  BY 1
HAVING sum(total) FILTER (WHERE department_id = 6) IS NOT NULL
ORDER  BY 1;

But first drop the redundant, expensive DISTINCT. Rows are bound to be distinct after GROUP BY has been applied. Also don't confuse DISTINCT (invoice_no) with DISTINCT ON (invoice_no). The first one has misleading parentheses that are stripped away. The second one has different meaning. See:

Also using the modern, faster FILTER clause for your conditional aggregates. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

If you want to filter null values you can put AND a6 IS NOT NULL in your WHERE condition

Whodi
  • 36
  • 3
  • 1
    Actually, to reference a6 in a where condition you'd have to wrap your current select in another select statement. – Whodi Dec 07 '18 at 19:54
  • @sgeddes please give me an example of creating sub query. I am new to these kind of complex queries. – Muhammad Iqbal Dec 07 '18 at 19:57
  • 1
    @MuhammadIqbal -- I'd personally use `having`, but if you want to use a subquery, it would look like this: `select * from (your query here without the order by) t where a6 is not null order by no`... – sgeddes Dec 07 '18 at 20:00
  • right I understand now that we can use sub query in where clause. thanks @sgeddes – Muhammad Iqbal Dec 07 '18 at 20:05
1

add HAVING a6 IS NOT NULL after the group by. So the query would become

select distinct (invoice_no) as no,sum(total), 
sum(case when department_id=2 then total end) as a2, 
sum(case when department_id=3 then total end) as a3, sum(case when department_id=4 then total end) as a4, 
sum(case when department_id=5 then total end) as a5, 
sum(case when department_id=6 then total end) as a6 from article_sale where invoice_date = '2018-10-01' 
group by no having sum(case when department_id=6 then total end) is not null order by no ASC
Roel van Endhoven
  • 180
  • 1
  • 11