1
   Row |day    |Prob     
--------------------------
    1   5   0.3  
    2   3   0.1 
    3   2   0.6 
    4   4   0.5  
    5   1   0.4

I want to merge day 1 to day 3 to a category and day 4 to 5 to another category how to do that? Prob of the category is not just simply adding each day's prob. Current code:

SELECT day, SUM( new ) / SUM( new+ old ) **AS** Prob
FROM mydata
WHERE year > 2005 and ( day >=1 and day <=5)
GROUP by day;
user3007324
  • 13
  • 3
  • Can you give your expected result here for the given data . – Ramesh Nov 19 '13 at 05:44
  • I don't know what the expected result should be honestly. Say, in day 1, there are 3 new task and 2 old ones. So prob of day 1 to choose a old task to do is 2/5. Day 2: new 1 old 1, prob 1/2; Day 3: new 2 old 3, prob 3/5. Now I want to form a category to show that the prob of choosing a old task from day 1 to day 2. So I expected it to be (2+1+3)/(5+2+5). – user3007324 Nov 19 '13 at 06:18

1 Answers1

0

SEE SQL FIDDLE

select '1' as CATEGORy,SUM(NEW_Task)/SUM(NEW_TASK+OLD_TASK) AS PROB from task

where day between 1 and 3

UNION

select '2' as CATEGORy,SUM(NEW_Task)/SUM(NEW_TASK+OLD_TASK) AS PROB from task

where day between 4 and 5

Based on what I understand from your question I just tried Query. Is this what you are trying to achieve or anything else

Ramesh
  • 1,872
  • 2
  • 20
  • 33
  • Thanks Ramesh! That's exactly what I want! The only problem left is that "UNION" doesn't work for bigquery..I can only get one category... – user3007324 Nov 19 '13 at 09:34
  • 1
    Alright I got it. To any of you who may be interested in how to do UNION in BigQuery: http://stackoverflow.com/questions/10644993/support-union-function-in-bigquery-sql – user3007324 Nov 19 '13 at 09:46
  • you can also use CASE, see http://stackoverflow.com/questions/15464574/use-google-bigquery-to-build-histogram-graph?rq=1 – Felipe Hoffa Nov 21 '13 at 00:15