Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got this query I'd like to get working. I'm trying to count all the instances of a foreign key after a certain date that isn't null, and then left join that to the distinct of the original table, so I can see the variables with no entries. Here's my query:

select fulllist.fk_fc_id 
from 
(
    select distinct fk_fc_id 
    from data_item
) as fulllist 
left outer join
(
    select temp.fk_fc_id, count(*) as number 
    from 
    (
        select * 
        from data_item 
        where di_item_value not like 'Null' 
            and di_timestamp > '2013-11-01 00:00:00'
    )   as temp 
    group by fk_fc_id
)   as lj
    on lj.fc_fk_id=fulllist.fk_fc_id;

the error is

ERROR: column lj.fc_fk_id does not exist

LINE 19: on lj.fc_fk_id=fulllist.fk_fc_id;

share|improve this question

2 Answers 2

up vote 8 down vote accepted

It looks like this is just a typo. Your subquery is returning fk_fc_id but your join is referencing fc_fk_id. It seems like you just need to alter the query:

select fulllist.fk_fc_id 
from 
(
    select distinct fk_fc_id 
    from data_item
) as fulllist 
left outer join
(
    select temp.fk_fc_id, count(*) as number 
    from 
    (
        select * 
        from data_item 
        where di_item_value not like 'Null' 
            and di_timestamp > '2013-11-01 00:00:00'
    )   as temp 
    group by fk_fc_id
)   as lj
    on lj.fk_fc_id=fulllist.fk_fc_id;

It also seems like you would want to include the number in the final select:

select fulllist.fk_fc_id, coalesce(lj.number, 0) number
from 
(
    select distinct fk_fc_id 
    from data_item
) as fulllist 
left outer join
(
    select temp.fk_fc_id, count(*) as number 
    from 
    (
        select * 
        from data_item 
        where di_item_value not like 'Null' 
            and di_timestamp > '2013-11-01 00:00:00'
    )   as temp 
    group by fk_fc_id
)   as lj
    on lj.fk_fc_id=fulllist.fk_fc_id;
share|improve this answer
    
Thanks, stupid case of getting confused, bah! –  James Dec 2 '13 at 15:11

Besides the typo (already found by @blueefet), your query can be simplified:

select fk_fc_id,
       COUNT( CASE WHEN di_item_value not like 'Null' 
                        and di_timestamp > '2013-11-01 00:00:00'
                   THEN 1 ELSE NULL END
            ) AS number 
from data_item 
group by fk_fc_id ;
share|improve this answer
1  
+1; or even just: di_item_value <> 'Null'. If that's really meant to exclude actual NULL values, then di_item_value IS NOT NULL, of course. –  Erwin Brandstetter Dec 2 '13 at 19:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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