Is this a imple Query?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Is this a imple Query?

  1. #1
    Join Date
    Nov 2001
    Posts
    118

    Is this a simple Query?

    Hi,

    Please excuse this dumb question, but I'm not a developer and perhaps it'll give you something to grin about. Anyway, here goes:

    I have a simple table containing three columns (I've simplified the examples):

    1) A list of streets
    2) a list of housenumbers for each of the above streets.
    3) A simple indicator whether each housenumber is occupied or not.

    I just want a result of a query that gives me the information in which of the streets all houses are occupied, which streets are partially occupied, and which streets are not occupied. What makes this tricky for me is that the result should only contain one row of the required information per street.

    I just can't figure it out. Any help, please?
    Last edited by Peer Jones; 03-31-2005 at 05:40 AM.

  2. #2
    Join Date
    Jan 2004
    Posts
    162
    Post sample data and output.

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Something like:
    Code:
    Select * from
    (select streetName,
     count(houseNumber)                over (partition by streetName) as tot_houses,
     sum(decode(occupiedFlag,'Y',1,0)) over (partition by streetName) as occ_houses)
    where occ_houses = tot_houses
    where occ_houses < tot_houses and occ_houses <> 0
    where occ_houses = 0

  4. #4
    Join Date
    Nov 2001
    Posts
    118
    Well, take this as a small example:

    The table would contain data like this:

    Street-----------House-No.------Occupied

    Street A-------------1-------------Y
    Street A-------------2-------------Y
    Street A-------------3-------------Y
    Street A-------------4-------------Y
    Street B-------------2-------------Y
    Street B-------------4-------------N
    Street B-------------5-------------Y
    Street C-------------3-------------N
    Street C-------------5-------------N

    (excuse the dashes, they're only for seperation purposes for this page)

    and the result should contain information like:

    Street A Occupied
    Street B PartiallY occupied
    Street C Unoccupied

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Code:
    Select Street,
    case 
    when occ_houses = tot_houses then 'Occupied'
    when occ_houses = 0 then 'Unoccupied'
    else 'Partially Occupied'
    end
    from
    (select Street,
     count(HouseNumber)            over (partition by Street) as tot_houses,
     sum(decode(Occupied,'Y',1,0)) over (partition by Street) as occ_houses)

  6. #6
    Join Date
    Jan 2004
    Posts
    162
    Dapi you are missing a FROM in there (previous attempt had 3 where clauses!). Otherwise that looks OK for occupation status by row.

    To group by street as post appears to require, try...
    Code:
    Personal Oracle Database 10g Release 10.1.0.2.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> CREATE TABLE table_name (
      2    street VARCHAR2 (8), 
      3    house_no NUMBER (1), 
      4    occupied VARCHAR2 (1));
    
    Table created.
    
    SQL> INSERT INTO table_name VALUES ('Street A', '1', 'Y');
    
    1 row created.
    
    SQL> INSERT INTO table_name VALUES ('Street A', '2', 'Y');
    
    1 row created.
    
    SQL> INSERT INTO table_name VALUES ('Street A', '3', 'Y');
    
    1 row created.
    
    SQL> INSERT INTO table_name VALUES ('Street A', '4', 'Y');
    
    1 row created.
    
    SQL> INSERT INTO table_name VALUES ('Street B', '2', 'Y');
    
    1 row created.
    
    SQL> INSERT INTO table_name VALUES ('Street B', '4', 'N');
    
    1 row created.
    
    SQL> INSERT INTO table_name VALUES ('Street B', '5', 'Y');
    
    1 row created.
    
    SQL> INSERT INTO table_name VALUES ('Street C', '3', 'N');
    
    1 row created.
    
    SQL> INSERT INTO table_name VALUES ('Street C', '5', 'N');
    
    1 row created.
    
    SQL> SELECT street, 
      2         DECODE (COUNT (DECODE (occupied, 'Y', 1)), 
      3           0, 'Unoccupied', 
      4           COUNT (*), 'Occupied', 
      5           'Partially Occupied') status
      6  FROM   table_name
      7  GROUP BY street;
    
    STREET   STATUS
    -------- ------------------
    Street A Occupied
    Street B Partially Occupied
    Street C Unoccupied
    
    SQL>

  7. #7
    Join Date
    Nov 2001
    Posts
    118
    Thanks, Guys, that really helped me a lot.

  8. #8
    Join Date
    Nov 2001
    Posts
    118
    Hi There,

    The solution by padders worked beautifully, but there's one thing I don't understand in that DECODE statement:

    What does the COUNT(*) part inside the DECODE do, and how does it work? I'd have thought it would need a FROM somewhere, and I can't find any reference to this anywhere.

    It's annoying for me that I can use it and it works, but I don't know how.

  9. #9
    Join Date
    Jan 2004
    Posts
    162
    The COUNT (*) return the number of rows per group as defined in the GROUP BY clause.

    COUNT (expression) return the number of rows per group where expression is NOT NULL. Since DECODE (occupied, 'Y', 1) returns either 1 or NULL then COUNT (DECODE (occupied, 'Y', 1)) returns the count of rows per group where occupied is 'Y'.

    Both of these functions can be used as part of other expressions, e.g. you could use COUNT (*) + 10 to return the number of rows per group plus 10.

    All the outer DECODE expression does is compare the number of rows per group where occupied = 'Y' with 0 (if true then all houses are 'Unoccupied') and then with the total number of rows (= houses) per group (if true then all houses are 'Occupied'). Any rows which fall into neither of these buckets are 'Partially Occupied' hence this is the 'ELSE' expression in the DECODE.

    All I do when faced with expressions I don't get is expand them. I might do something like this...

    Code:
    Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.4.0 - Production
    
    SQL> SELECT street, 
      2         COUNT (DECODE (occupied, 'Y', 1)) occupied, 
      3         COUNT (*) total, 
      4         DECODE (COUNT (DECODE (occupied, 'Y', 1)), 
      5           0, 'Unoccupied', 
      6           COUNT (*), 'Occupied', 
      7           'Partially Occupied') status
      8  FROM   table_name
      9  GROUP BY street;
    
    STREET     OCCUPIED      TOTAL STATUS
    -------- ---------- ---------- ------------------
    Street A          4          4 Occupied
    Street B          2          3 Partially Occupied
    Street C          0          2 Unoccupied
    
    SQL>
    Is that of any help?

  10. #10
    Join Date
    Nov 2001
    Posts
    118
    Thank you, Padders, that made it much clearer for me.

    I don't want to get on your nerves, but I'm still stuck on that other thread I posted about the student-qualification problem. Would you mind having a look at it and giving me your opinion?

    This is still bugging me terribly and I want to start writing a procedure instead with IF statements and so on, but the DECODE possibilities seem so elegant.

    I'd be very thankful for your help.

Posting Permissions

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



Click Here to Expand Forum to Full Width