Is this a imple Query?

[Peer Jones]

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?

[padders]

Post sample data and output.

[DaPi]

Something like:

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

[Peer Jones]

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

[DaPi]

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)

[padders]

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...

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>

[Peer Jones]

Thanks, Guys, that really helped me a lot.

[Peer Jones]

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.

[padders]

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...

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?

[Peer Jones]

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.