R help - Jan 2012 - Using !is.na() in a HAVING clause in sqldf() XXXX

Hi everyone,

I have the following:

sqldf("select Premie,count(tpounds) N,avg(tpounds) Avg_Weight,
  stddev_samp(tpounds) StdDev
  from children
  group by Premie
  having !is.na(Premie)")

sqldf() does not like the !is.na(Premie) specification. How does one
exclude a "missing" group in an aggregated query using sqldf()?

Thanks!

Dan

	[[alternative HTML version deleted]]

Did you try a where statement?

where Premie is not null

On Tue, Jan 17, 2012 at 3:03 PM, Dan Abner <dan.abner99@gmail.com> wrote:
> Hi everyone,
>
> I have the following:
>
> sqldf("select Premie,count(tpounds) N,avg(tpounds) Avg_Weight,
>  stddev_samp(tpounds) StdDev
>  from children
>  group by Premie
>  having !is.na(Premie)")
>
> sqldf() does not like the !is.na(Premie) specification. How does one
> exclude a "missing" group in an aggregated query using sqldf()?
>
> Thanks!
>
> Dan
>
>        [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help@r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
>
http://www.R-project.org/posting-guide.html<http://www.r-project.org/posting-guide.html>
> and provide commented, minimal, self-contained, reproducible code.
>


-- 
Joseph C. Magagnoli

	[[alternative HTML version deleted]]

Dan -
    Try using "having Premie not null" instead of 
"having !is.na(Premie)" .
 					- Phil Spector
 					 Statistical Computing Facility
 					 Department of Statistics
 					 UC Berkeley
 					 spector at stat.berkeley.edu


On Tue, 17 Jan 2012, Dan Abner wrote:
> Hi everyone,
>
> I have the following:
>
> sqldf("select Premie,count(tpounds) N,avg(tpounds) Avg_Weight,
>  stddev_samp(tpounds) StdDev
>  from children
>  group by Premie
>  having !is.na(Premie)")
>
> sqldf() does not like the !is.na(Premie) specification. How does one
> exclude a "missing" group in an aggregated query using sqldf()?
>
> Thanks!
>
> Dan
>
> 	[[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>

"having" is right; use "HAVING Premie IS NOT NULL" instead. 
The sqldf
package has a SQLite database running behind it.  All NA get internally 
converted to NULL, which is the standard representation for N/A in SQL, and 
then they become <NA> in the data.frame that is returned by the sqldf 
command

Cheers!.

On Tuesday, January 17, 2012 1:03:20 PM UTC-8, Dan Abner
wrote:
>
> Hi everyone,
>
> I have the following:
>
> sqldf("select Premie,count(tpounds) N,avg(tpounds) Avg_Weight,
>   stddev_samp(tpounds) StdDev
>   from children
>   group by Premie
>   having !is.na(Premie)")
>
> sqldf() does not like the !is.na(Premie) specification. How does one
> exclude a "missing" group in an aggregated query using sqldf()?
>
> Thanks!
>
> Dan
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> R-h... at r-project.org <javascript:> mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide 
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
>