I have this select statement:
SELECT p.WPD, count(b.PID) as ParcelsPerBlck,
(SELECT count(PID)
FROM bos05eb
WHERE ptype BETWEEN 009 AND 133) as ResdOnly
FROM ebos_parcels05 p, bos05eb b
WHERE p.PID_LONG = b.PID
GROUP BY p.WPD;
I want the results of the sub-query in the select statement to also group by p.WPD. But I get the result of the query in each row as follows:
WPD ParcelsPerBlck ResdOnly
01-02-030 38 6198
01-08-035 56 6198
01-04-060 11 6198
and so on.
Can the results of a sub-query not be grouped so that I only get the result count of the sub-query ResdOnly for each row?
You can use conditional aggregation for this without the need for a correlated subquery:
select
p.WPD,
count(b.PID) as ParcelsPerBlck,
sum(case when b.ptype between 009 AND 133 then 1 else 0 end) as ResdOnly
from
ebos_parcels05 p
join
bos05eb b on p.PID_LONG = b.PID
group by
p.WPD
Please note the usage of the explicit join syntax -- no need to use commas in the from clause.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments