Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: combining 2 SQL queries - 10g

    Hi guys

    Looking for some help in writing a query:

    I have a table containing 24 values for each for each of the 24hrs of the day for 2 nodes (AA,BB) with each NODE having 10 IDs as shown below:

    Code:
    DATETIME  ID NODE STATS
    1/9/2012 00:00     1   AA      567
    1/9/2012 01:00     1   AA      123
    ....
    1/9/2012 23:00     1   AA      1000       (24 values)
    1/9/2012 00:00     2   AA      234
    1/9/2012 01:00     2   AA      675
    ....
    1/9/2012 23:00     2   AA      1010       (24 values)
    .....
    .....
    .....
    1/9/2012 00:00     10   AA      4563
    1/9/2012 00:00     10   AA      5677
    ....
    1/9/2012 23:00     10   AA      1020         (24 values)
    1/9/2012 00:00     11   BB      567
    1/9/2012 01:00     11   BB      123
    ....
    1/9/2012 23:00     11   BB      1000       (24 values)
    .....
    .....
    .....
    1/9/2012 00:00     20    BB      4563
    1/9/2012 01:00     20    BB      5677
    ....
    1/9/2012 23:00     20    BB     1020         (24 values)
    I need to write a query as shown below by specifying the ID because there are more IDs which I haven't mentioned above for the sake of simplicity:

    Code:
    SELECT TRUNC(DATETIME,'HH'), NODE NODEAA, SUM(STATS) STATSAA FROM TABLE WHERE ID IN (1,2,34,5) GROUP BY TRUNC(DATETIME,'HH'), NODE
    & another query:

    Code:
    SELECT TRUNC(DATETIME,'HH'), NODE NODEBB, SUM(STATS) STATSBB FROM TABLE WHERE ID IN (10,11,12,13,14) GROUP BY TRUNC(DATETIME,'HH'), NODE
    Q: Will appreciate if someone can advise how I can use the 2 queries above, to display the result as below:

    Code:
    DATETIME, NODEAA, STATSAA NODEBB, STATSBB STATSAA+STATSBB
    NOTE: Although I'm grouping by TRUNC(DATETIME,HH), I still need to use SUM(STATS) for further processing.

    Regards
    Shajju

  2. #2
    Join Date
    Jul 2012
    Location
    Bangalore
    Posts
    8
    Why don't you try using 'With' clause...

    Regards
    Abhinav

Posting Permissions

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