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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

closed as off-topic by Paul White Mar 14 at 13:04

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

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

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

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