PostgreSQL - get records with null values

Question

I'm trying to get a query which would show distributors that haven't sell anything in 90 days, but the problem I get is with NULL values. It seems PostgreSQL ignores null values, even when I queried to show it (or maybe I did it in wrong way).

Let say there are 1000 distributors, but with this query I only get 1 distributor, but there should be more distributors that didn't sell anything, because if I write SQL query to show distributors that sold by any amount in the last 90 days, it shows about 500. So I wonder where are those other 499? If I understand correctly, those other 499, didn't have any sales, so all records are null and are not showed in query.

Does anyone know how to make it show null values of one table where in relation other table is not null? (like partners table (res_partner) is not null, but sale_order table (sales) or object is null? (I also tried to filter like so.id IS NULL, but in such way I get empty query)

Code of my query:

(
SELECT
    min(f1.id) as id,
    f1.partner as partner,
    f1.sum1
       FROM
    (
       SELECT
          min(f2.id) as id,
          f2.partner as partner,
          sum(f2.null_sum) as sum1
       FROM
    (
    SELECT
          min(rp.id) as id,
          rp.search_name as partner,
     CASE
       WHEN
             sol.price_subtotal IS NULL
       THEN
             0
       ELSE
             sol.price_subtotal
     END as null_sum
    FROM
          sale_order as so,
          sale_order_line as sol,
          res_partner as rp
    WHERE
    sol.order_id=so.id and
    so.partner_id=rp.id
    and
    rp.distributor=TRUE
    and
    so.date_order <= now()::timestamp::date
    and
    so.date_order >= date_trunc('day', now() - '90 day'::interval)::timestamp::date
    and 
    rp.contract_date <= date_trunc('day', now() - '90 day'::interval)::timestamp::date
    GROUP BY
    partner,
    null_sum
   )as f2
   GROUP BY
   partner
  ) as f1
WHERE
sum1=0
GROUP BY
partner,
sum1
)as fld

EDIT: 2012-09-18 11 AM.

I think I understand why Postgresql behaves like this. It is because of the time interval. It checks if there is any not null value in that inverval. So it only found one record, because that record had sale order with zero (it was not converted from null to zero) and part which checked for null values was just skipped. If I delete time interval, then I would see all distributors that didn't sell anything at all. But with time interval for some reason it stops checking null values and looks if there are only not null values.

So does anyone know how to make it check for null values too in given interval?.. (for the last 90 days to be exact)

Are you able to give the create table statements? I'm thinking of outer join queries ... — wollud1969
– wollud1969, Commented Sep 17, 2012 at 12:43

user330315 · Accepted Answer · 2012-09-17 12:52:16Z

2

Aggregates like sum() and and min() do ignore NULL values. This is required by the SQL standard and every DBMS I know behaves like that.

If you want to treat a NULL value as e.g. a zero, then use something like this:

sum(coalesce(f2.null_sum, 0)) as sum1

But as far as I understand you question and your invalid query you actually want an outer join between res_partner and the sales tables.

Something like this:

SELECT min(rp.id) as id,
       rp.search_name as partner,
       sum(coalesce(sol.price_subtotal,0)) as price_subtotal
FROM res_partner as rp 
  LEFT JOIN sale_order as so ON so.partner_id=rp.id and rp.distributor=TRUE
  LEFT JOIN sale_order_line as sol ON sol.order_id=so.id 
WHERE so.date_order <= CURRENT_DATE
  and so.date_order >= date_trunc('day', now() - '90 day'::interval)::timestamp::date
  and rp.contract_date <= date_trunc('day', now() - '90 day'::interval)::timestamp::date
GROUP BY rp.search_name

I'm not 100% sure I understood your problem correctly, but it might give you a headstart.

answered Sep 17, 2012 at 12:52

user330315

Sign up to request clarification or add additional context in comments.

6 Comments

Andrius Over a year ago

I get same result with your solution. Maybe something is wrong with database.. How it could not show any results, when I change interval to let say 1 day? But it shows when interval is bigger (90 days). There should be much more distributors that didn't sell anything than in the last 90 days, but it doesn't show any distributors that didn't sell yesterday, but it shows one distributor that didn't sell in the last 90 days. That's strange.

user330315 Over a year ago

@oerp: without showing us some sample data and the real statement you are running it's impossible to tell what is going wrong on your side.

Andrius Over a year ago

Ok. If I use you suggestion, I get 247 records (one which has sum of zero). If I add in WHERE sum1=0, I get 1 record that has sum of zero. I i change so.date_order >= date_trunc('day', now() - '90 day'::interval)::timestamp::date here 90 to 1, I get no records. If I leave interval as one day, but delete sum1=0, I get 23 records (and none are with sum of zero). If I just write SQL query to show rp.search_name WHERE rp.distributor=TRUE and rp.contract_date <= date_trunc('day', now() - '90 day'::interval)::timestamp::date I get 529 records.

Andrius Over a year ago

So there are about half records that are not showing up for unknown reasons (well at least I don't know why)

Andrius Over a year ago

I was thinking and I started wondering, how postgresql defines when value is null at particular time? When it is checking for 90 days, so how much null values it gets in let say a day?.. Why I'm wondering this, is because if one column is null for some partners (sale orders) and partners column is not (always not null because it ignores null values), how many 'zeros' it gets per day if any on a given partners?.. So maybe there is a way to just set partner sales sum as zero, if there were not any sale orders (that column is null for those distributors/partners) in 90 days?

|

Piotr Wadas · Accepted Answer · 2012-09-17 12:50:54Z

0

Try to name subqueries, and retrieve their columns with col.q1, col.q2 etc. to make sure which column from which query/subquery you're dealing with. Maybe it's somewhat simple, e.g. it unites some rows containing only NULLs into one row? Also, at least for debugging purposes, it's smart to add , count(*) at the end of each query/subquery to get implicit number of rows returned on result.. hard to guess what exactly happened..

edited Sep 17, 2012 at 12:50

answered Sep 17, 2012 at 12:41

Piotr Wadas

1,8841 gold badge10 silver badges13 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - get records with null values

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related