-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
> > specific pattern in the URL. This is very simple with a select:
> > SELECT day, count(1) FROM accesslogs WHERE request like '%foo%' group by
> > 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