Home | About | Sematext search-lucene.com search-hadoop.com
 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?
Vijay 2009-08-14, 01:00
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
>