0

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 ?

  • 1
    COUNT(so.type) will never return null, so COALESCE(COUNT(so.type),0) wont work the way you would like. – Lennart Jul 15 at 12:49
  • You should add CREATE TABLE statements together with INSERT statements for sample data, or use dbfiddle.uk/?rdbms=postgres_11 or similar. – Lennart Jul 15 at 12:51
  • SELECT city, ..., SUM(CASE WHEN type='Man' THEN quantity ELSE 0 END) AS "N man", ... FROM ... GROUP BY city ... – Akina Jul 15 at 12:54
  • thanks @Akina :) – Antonio Faienza Jul 15 at 13:21
0

Use the FILTER clause

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.