Postgresql: How to insert row value in the output even if it is not present in column

Question

let table name "box" the box will have

1. pen
2. pencil
3. eraser
4. sharpener
5. scale

but may SQL table looks like this,

s no.___    item____  quantity

 1 _____       pen_____     2

 2 ____      pencil____   3

 3____      eraser____    5

i need a query, which gives me the following output

item______      quantity

pen ______          2

pencil ____       3

eraser ____       5

sharpener__     0

scale   ____      0

This is the query i am using right now

select CASE disease_confirmed when 'Forward to EMMINENT Cardiologist' then 'Repeat ECG Within 10 Minutes' when 'Yes' then 'Yes' when 'No' then 'No' when 'Re-take ECG' then 'Re-take ECG' when 'Reassign to 1 Cardiologist' then 'Reassign to 1 Cardiologist' when 'Reassign to 2 Cardiologists' then 'Reassign to 2 Cardiologists' when 'Reassign to 3 Cardiologists' then 'Reassign to 3 Cardiologists'

END AS disease_confirmed,

count(disease_confirmed) from immi_referral_tb where created_date>= '2015-11-01 00:00'::timestamp AND created_date<= '2015-11-30 23:59'::timestamp group by disease_confirmed union all SELECT 'Total',count(disease_confirmed) from immi_referral_tb where created_date>= '2015-11-01 00:00'::timestamp AND created_date<= '2015-11-30 23:59'::timestamp;

output:

click here for output image

so i need the following values under "disease confirmed column" Re-take ECG Yes Reassign to 3 Cardiologists Repeat ECG Reassign to 1 Cardiologist No Reassign to 2 Cardiologists

but according to my query i have 1 month time period, so some of the above values are not present in the column for that time period.

so, need those all values in the output like null '0' if there are not present in the column.

This is completly unclear. Post your table schema and sample of your table data with the desired output. — java
– java, Commented Feb 16, 2016 at 7:48
Where do you contain all the items names?(without the quantity only the names) — sagi
– sagi, Commented Feb 16, 2016 at 7:50

Mureinik · Accepted Answer · 2016-02-16 07:55:06Z

2

You could create another table with the expected items and left join on it:

CREATE TABLE expected (item VARCHAR(10));

INSERT INTO expected
VALUES ('pen'), ('pencil'), ('eraser'), ('sharpener'), ('scale');

SELECT    e.item, COALESCE(b.quantity, 0)
FROM      expected e
LEFT JOIN box b ON e.item = b.item

If you don't want to create another table you could use a query that selects hard-coded literals instead:

SELECT    e.item, COALESCE(b.quantity, 0)
FROM      (SELECT 'pen' AS item
           UNION ALL
           SELECT 'pencil'
           UNION ALL
           SELECT 'eraser'
           UNION ALL
           SELECT 'sharpener'
           UNION ALL
           SELECT 'scale') e
LEFT JOIN box b ON e.item = b.item

answered Feb 16, 2016 at 7:55

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

5 Comments

user330315 Over a year ago

You don't actually need the create table. You could use a cte or a derived table directly with the values clause from the insert

Y A N I V Over a year ago

I have one more question, can i use 'count' in this statement like ""SELECT e.item, COALESCE(b.count(item), 0)"" in the first line ?? i tried like that but it is showing some ambigious error. please help .

Mureinik Over a year ago

@saivenkatavinaypasupuleti can you share the exact SQL you're using and the error's text?

Y A N I V Over a year ago

SELECT e.disease_confirmed, COALESCE(b.count(disease_confirmed), 0) FROM (SELECT 'Yes' AS disease_confirmed UNION ALL SELECT 'No' UNION ALL SELECT 'Forward to EMMINENT Cardiologist' UNION ALL SELECT 'Re-take ECG' UNION ALL SELECT 'Reassign to 1 Cardiologist' UNION ALL SELECT 'Reassign to 2 Cardiologists' UNION ALL SELECT 'Reassign to 3 Cardiologists') e LEFT JOIN immi_referral_tb b ON e.disease_confirmed = b.disease_confirmed ERROR: column reference "disease_confirmed" is ambiguous LINE 1: SELECT e.disease_confirmed, COALESCE(b.count(disease_conf... SQL state: 42702 Character: 49

Y A N I V Over a year ago

sorry for confusing comment, it is not allowing more characters

Collectives™ on Stack Overflow

Postgresql: How to insert row value in the output even if it is not present in column

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related