Postgresql – How to count number of occurrences for all different values in database column and obtain 0 if are there empty result

postgresql

I'am newby with postgresSQL.
I have a table with this structured:

city          | type     | quantity | sellout   | sellout_update
"ACIREALE"    | "Man"    |  "3"     |"1100.0"   |"366.6666666666666667"
"ACIREALE"    | "Unisex" |  "3"     | "1405.0"  |"468.3333333333333333"
"ACIREALE"    | "Woman"  |  "9"     | "2480.0"  |"275.5555555555555556"
"ALESSANDRIA" | "Kid"    |  "1"     |"100.0"    |"100.0000000000000000"
"ALESSANDRIA" | "Man"    |  "13"    | "3500.0"  |"269.2307692307692308"
"ALESSANDRIA" | "Unisex" |  "6"     | "1485.0"  |"247.5000000000000000"
"ALESSANDRIA" | "Woman"  |  "29"    | "8085.0"  | "278.7931034482758621"

I obtain this result from this query:

select lg.city, 
        so.type, 
        SUM(so.sell_out_quantity)  as quantity, 
        SUM(so.estimated_sell_out_value_based_on_srp_euro5) as sellout,
        (SUM(so.estimated_sell_out_value_based_on_srp_euro5)/SUM(so.sell_out_quantity )) as sellout_update 
FROM doors As lg INNER JOIN sellout as so ON lg.code = so.code
GROUP BY lg.city, 
         so.type

now, i want obtain this result:

city        | N man | N Unisex  | N Woman | N Kid | ...   | sellout
"ACIREALE"    | 3     |  "3"      |   9     | **0**     | ...
"ALESSANDRIA" | 1     |  13       |   6     | 29    | ....

For each row I want count the number of occurrences for all different values and transpose them in a specific column. I think that i need to different subquery and join them. For Example:

...
INNER JOIN (select lg.city, 
                 COALESCE(COUNT(so.type),0) as c_kid                    
                FROM doors As lg INNER JOIN sellout as so ON lg.code = 
                so.code
                WHERE so.type = 'Kid'                   
                GROUP BY lg.city 
                         ) As count_kid ON lg.city = count_kid.city
...

It work. (I don't know if exist the a better way to achieve this result)
As it can be see, i use COALESCE function for obtain a 0 if a value doesn't exist but after several attempts I always get an empty line if it doesn't exist. Where am I doing wrong ?

Best Answer

Use the FILTER clause

SELECT city,
       sum(quantity::integer) FILTER (WHERE type = 'Man') AS man,
       ...
FROM doors JOIN ...
GROUP BY doors.city, ...;

Related Solutions

PostgreSQL – Find All Values Not Present for Other Column Values

If I interpret your question correctly:

"Pick all rows where a has one of two given values, and b only exists in combination with one of them."

You can use any of the standard techniques laid out in the referenced answer.

Select rows where value of second column is not present in first column

Just restrict your base table to the two given a. For instance

SELECT *
FROM   tbl t1
WHERE  a IN (1,2)
AND    NOT EXISTS (
   SELECT 1
   FROM   tbl t2
   WHERE  a IN (1,2)
   AND    t2.b =  t1.b
   AND    t2.a <> t1.a
   );

SQL Fiddle

This also works with duplicates on (a,b). Multiple identical rows would be returned.

PostgreSQL Arrays – How Does unnest() Treat NULL Values and Empty Arrays?

To quote Tom Lane in the thread "Should array_length() Return NULL" on pgsql-hackers:

The thing is that that syntax creates an array of zero dimensions, not one that has 1 dimension and zero elements. So "0" would be incorrect.

There is a related TODO item in the Postgres Wiki.

Hard to say whether array_length() should return NULL or 0 for an empty array dimension ...

Solution

You could invert the logic and process the original source array only if the length is >= 1:

WITH tbl(id, arr) AS (
   VALUES
     (1, '{a}'::text[])
   , (2, '{NULL}'::text[])
   , (3, '{}'::text[])
   , (4, '{x,y,z}'::text[])
   )
SELECT id, elem
FROM   tbl t
     , unnest (
          CASE WHEN array_length(t.arr, 1) >= 1
               THEN t.arr
               ELSE '{null}'::text[] END
       ) elem;

Returns at least one row for each source row.

Or, much simpler, use an actual LEFT JOIN (the comma in your FROM clause is short notation for CROSS JOIN):

SELECT id, elem
FROM   tbl t
LEFT   JOIN LATERAL unnest(t.arr) elem ON true;

This fills in NULL values automatically for columns of the right table if no match is found. Exactly what you are trying to achieve.
Related:

What is the difference between LATERAL and a subquery in PostgreSQL?

db<>fiddle here

Best Answer

Related Solutions

PostgreSQL – Find All Values Not Present for Other Column Values

PostgreSQL Arrays – How Does unnest() Treat NULL Values and Empty Arrays?

Solution

Related Question