4

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;

closed as off-topic by Paul White Mar 14 '16 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.

8

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;
5

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 ;
  • 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.