Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2008
    Posts
    5

    Unanswered: merge these 2 queries

    Hello,
    I am trying to merge these 2 queries into one so the resulting table uses all the criteria.

    The ONLY difference in the queries is once has a deviceaddress = 9 and the other has deviceaddress =8.

    I tried deviceaddress =9 AND deviceaddress =8 but it did not produce the correct result using the min() max() functions.
    Any help would be appreciated.

    Drew

    Code:
    SELECT (max(ch1kwh) - min(ch1kwh)) as kwh, date_trunc('day', date) as date 
    FROM monitordata 
    WHERE date >= date_trunc('day', timestamp '4/1/2009') 
    AND date <= date_trunc('day', timestamp '4/30/2009') 
    AND (deviceaddress =9) 
    GROUP BY date_trunc('day', date) 
    
    
    SELECT (max(ch1kwh) - min(ch1kwh)) as kwh, date_trunc('day', date) as date 
    FROM monitordata 
    WHERE date >= date_trunc('day', timestamp '4/1/2009') 
    AND date <= date_trunc('day', timestamp '4/30/2009') 
    AND (deviceaddress =8) 
    GROUP BY date_trunc('day', date)

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by cgishack
    I tried deviceaddress =9 AND deviceaddress =8 but it did not produce the correct result using the min() max() functions.
    Just think about that condition for a moment.
    You are asking the database to return all rows where the column deviceaddress has the value 9 and at the same time has the value 8.
    Hardly possible, right

    You didn't clearly state what you want, but from what I have understood you probably want
    deviceaddress = 9 OR deviceaddress = 8.

    As an alternative you can also use the IN operator
    deviceaddress IN (8,9)

  3. #3
    Join Date
    Mar 2008
    Posts
    5
    Thanks for the reply shammat,
    I have tried OR and as you explained it does make sense, but it does not produce the correct result.

    If I run the first query the first row = 52
    If I run the second query the first row = 2.5

    When i combine them using deviceaddress = 9 OR deviceaddress = 8, the first row produces a number larger than 3000, when i am expecting 54.5

    I guess i need the max(ch1kwh) - min(ch1kwh) WHERE deviceaddress =9 PLUS (+) max(ch1kwh) - min(ch1kwh) WHERE deviceaddress =8

    Is there anyway I can phrase that?

    Drew

  4. #4
    Join Date
    Nov 2008
    Posts
    7
    Code:
    SELECT (max(ch1kwh) - min(ch1kwh)) as kwh, date_trunc('day', date) as date 
    FROM monitordata 
    WHERE date >= date_trunc('day', timestamp '4/1/2009') 
    AND date <= date_trunc('day', timestamp '4/30/2009') 
    AND deviceaddress IN (8,9)
    GROUP BY date_trunc('day', date) 
    
    
    SELECT (max(ch1kwh) - min(ch1kwh)) as kwh, date_trunc('day', date) as date 
    FROM monitordata 
    WHERE date >= date_trunc('day', timestamp '4/1/2009') 
    AND date <= date_trunc('day', timestamp '4/30/2009') 
    AND (deviceaddress=8 OR deviceaddress=9) 
    GROUP BY date_trunc('day', date)
    Those two queries really should do what you need and produce identical results.
    Are you sure you had those round brackets there when you were trying it with deviceaddress=8 OR deviceaddress=9 ?

    EDIT: sorry, I didn't see comments #3 and #4.
    Last edited by slapo; 04-29-09 at 11:21.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •