Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Some questions on hive SELECT/UNION - how to do multiple counts in one query?


Copy link to this message
-
Re: Some questions on hive SELECT/UNION - how to do multiple counts in one query?
Thank you so much Zheng! You made my day!! I should've spent some time
brushing up on SQL :)

On Thu, Aug 13, 2009 at 4:48 PM, Zheng Shao <[EMAIL PROTECTED]> wrote:

> SELECT day,
>  SUM(IF(request like '%foo%', 1, 0)),
>  SUM(IF(request like '%bar%', 1, 0))
> FROM accesslogs
> group by day
> order by day;
>
>
> On Thu, Aug 13, 2009 at 4:42 PM, Vijay<[EMAIL PROTECTED]> wrote:
> > Hi,
> >
> > I have some questions about using SELECT with UNION. I have a number of
> > access log files that I want to process to select counts per day matching
> a
> > specific pattern in the URL. This is very simple with a select:
> >
> > SELECT day, count(1) FROM accesslogs WHERE request like '%foo%' group by
> day
> > order by day;
> >
> > However, when I need to count multiple patterns (count of requests like
> > '%foo%', like '%bar%', etc), I'm not able to find the right query. I made
> > some attempts with UNION ALL and such but none of them really worked.
> > Ultimately I'm trying to get some numbers like this:
> >
> > <day>, <count matching %foo%>, <count matching %bar%>
> >
> > Is there a way of doing this with a single query? I know I can do it with
> > multiple queries and such but that seems like such a waste of time and
> > resources.
> >
> > I hope my question is clear.
> >
> > Thanks,
> > Vijay
> >
> >
>
>
>
> --
> Yours,
> Zheng
>
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB