return 0 1

[ksbrace]

I am trying to return all portfolios, even if they don not have any that have NEWBIZ (return 0 with portfolio). I tried IFNULL. Not sure how to go about it. Any advice?

select ifnull(count(*),0) ,d.portfolio from dbase d, portfolios pf where statusname='NEWBIZ'
and pf.portfolio=d.portfolio 
group by pf.portfolio order by pf.placementdate;
[code]

 

[IanWaterman]

Try

select d.portfolio,
sum(case when pf.portfolio is null then 1 else 0 end) PFCount
from dbase d
left outer join portfolios pf
on d.portfolio=pf.portfolio
and pf.statusname='NEWBIZ'
group by d.portfolio
order by pf.placementdate;

Ian

 

[r937]

COUNT() will return 0 if there weren't any matches in a LEFT OUTER JOIN (provided you count a column from the right table)

also, in a GROUP BY query, in order to sort on a column, that column must be in the SELECT list -- yes mysql will execute it regardless, but it might give the wrong data

SELECT d.portfolio
     , MAX(pf.placementdate) AS maxdate
     , COUNT(pf.portfolio) AS PFCount
 FROM dbase AS d
LEFT OUTER 
  JOIN portfolios AS pf 
    ON pf.portfolio = d.portfolio 
   AND pf.statusname = 'NEWBIZ'
GROUP 
    BY d.portfolio 
ORDER 
    BY maxdate;

note also that dbases which are missing portfolios will have NULL as their placement date, so these will sort first


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ksbrace]

Thanks! Works perfect