SQL> SELECT &®ion "Region",
2 SUM(price) "Total Sales",
3 (SELECT sum(count(property_id)) FROM hh_dwellings WHERE house_id IN
4 (SELECT hh_deeds.house_id FROM hh_sales, hh_deeds WHERE office_id IN
5 (SELECT office_id FROM hh_office WHERE region=&®ion)
6 AND hh_deeds.deed_id = hh_sales.deed_id)
7 GROUP BY property_id) "Properties sold"
8 FROM hh_sales
9 WHERE office_id IN
10 (SELECT office_id FROM hh_office WHERE region = &®ion)
11 ;
Enter value for region: SE
old 1: SELECT &®ion "Region",
new 1: SELECT SE "Region",
old 5: (SELECT office_id FROM hh_office WHERE region=&®ion)
new 5: (SELECT office_id FROM hh_office WHERE region=SE)
old 10: (SELECT office_id FROM hh_office WHERE region = &®ion)
new 10: (SELECT office_id FROM hh_office WHERE region = SE)
(SELECT sum(count(property_id)) FROM hh_dwellings WHERE house_id IN
*
ERROR at line 3:
ORA-00937: not a single-group group function
As far as I can see im only picking out 1 column for the count function but its acting like there are multiple columns.
When run by itself.....
SELECT sum(count(property_id)) FROM hh_dwellings WHERE house_id IN
(SELECT hh_deeds.house_id FROM hh_sales, hh_deeds WHERE office_id IN
(SELECT office_id FROM hh_office WHERE region=&®ion)
AND hh_deeds.deed_id = hh_sales.deed_id)
GROUP BY property_id
......it returns successfully
I think its your grouping thats blowing it out. Why are you grouping by house_id if you want a count of all of the properties anyway ?
Updated it with some more changes....
When you run it by itself does it return a single value (the sum of the count) ?