Previous Next

• 07-31-2008, 6:25 PM

  wlau Joined on 07-31-2008, 5:46 PM Posts 5

  SUM with multiple condition Reply Contact Hi, I'm new using LP, need help to get SUM with multiple condition. Example: Column1(Text), Column2(Integer), Column3(Integer) If condition match Column1 and Column2 then get SUM of Column3 as Result1.  I have many different combination, looking for query that can give me Result1, 2, 3, 4.... Then on top need to do a simple SUM of Column2 as Result5 and Column3 as Result6.  Not sure if this can be done in one pass with one query, if not just getting help with the first query would be enough to get me rolling. Thanks in advance.

  Tags: help

• 07-31-2008, 6:54 PM

  In reply to

  yellowdog.dave Joined on 07-18-2008, 3:17 AM Johannesburg, South Africa Posts 66

  Re: SUM with multiple condition Reply Contact  Hi,  Not sure what you are trying to achieve here, but a simple example based on a file called example.csv which looks as follows;  String,Integer,Result a,1,3 b,2,4 c,3,5 a,1,7 Would return these results when given this query; logparser "select String, Integer, sum(Result) as Sum from example.csv group by String, Integer" -i:csv String Integer Sum ------ ------- --- a      1       10 b      2       4 c      3       5 And would these results when given this query; logparser "select String, Integer, sum(Result) as Sum from example.csv group by String, Integer having String='a' and Integer=1" -i:csv    String Integer Sum ------ ------- --- a      1       10 You could then exend the HAVING clause for specifics and the CASE statement to take care of other logic. Hope this helps.  Good luck.  Cheers, Dave      Yes, dear

• 07-31-2008, 8:13 PM

  In reply to

  wlau Joined on 07-31-2008, 5:46 PM Posts 5

  Re: SUM with multiple condition Reply Contact Dave, thanks to your reply. I guess I didn't properly give my example, and I have already done exactly what you describe in your example. This is where I'm stuck, using HAVING would not solve my problem since it will only return one condition. I need to get the TOTAL of the SUM if Integer is Between 100 to 200 as Result1, 201 to 300 as Result2, and so on, I want to use CASE but can't figure out how to do it.

  Tags: help

• 07-31-2008, 8:48 PM

  In reply to

  yellowdog.dave Joined on 07-18-2008, 3:17 AM Johannesburg, South Africa Posts 66	Re: SUM with multiple condition Reply Contact  Have you tried the GROUPING and ROLLUP functions??? Yes, dear

• 08-02-2008, 1:04 AM

  In reply to

  wlau Joined on 07-31-2008, 5:46 PM Posts 5	Re: SUM with multiple condition Reply Contact Dave,  Sorry!  I couldn't figure out how to use GROUPING with condition either.  Can you show an example. Or anyone reading this post. - Thanks in advance!!!!!
  	Tags: help

• 08-04-2008, 2:05 AM

  In reply to

  yellowdog.dave Joined on 07-18-2008, 3:17 AM Johannesburg, South Africa Posts 66

  Re: SUM with multiple condition Reply Contact Okay, I think I may finally have a solution for you. If you use the QUANTIZE function, you should be able to get what it sounds like you want to do. Maybe not exactly in the format you need, but it should do.  My input looks as follows; String,Integer,Result a,160,3 b,210,4 c,305,5 a,101,7 My query looks as follows; logparser "select String, quantize(Integer,100) as Interval, sum(Result) as Sum from example.csv group by String, Interval" -i:csv My results look as follows;   String Interval Sum ------ -------- --- a      100      10 b      200      4 c      300      5 I know that it is rounding down, but if you use 0 based arithmetic, it should give you what you want.  Hope this helps.  Cheers, Dave    Yes, dear

• 08-05-2008, 4:18 AM

  In reply to

  wlau Joined on 07-31-2008, 5:46 PM Posts 5	Re: SUM with multiple condition Reply Contact Dave, I would have never thought of using QUANTIZE, I'm getting the exact and perfect output with little tweaks here and there. With expert advise like yours, you make LP possible for anything and everything.  Really appreciate your expert advise and time. Regards, - Winson
  	Tags: help