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;

| improve this 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;
| improve this answer | | | | |
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 ;
| 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.