select count(distinct consumerno), sum (case when ccon like '%D%' then 1 else 0 end) as Domestic, sum (case when ccon like '%B%' then 1 else 0 end) as Business from consumer left join contract on consumerno = cconsumer where status = 'Active'
total B D 35952 35694 1669
There are two tables : consumer & contract
What I am trying to achieve here is get total number of consumers who buy product starts with business or domestic. Contract table has got duplicate consumer numbers as they change from business to domestic or vice verse. As you can see from above query total is right but sum for domestic & business is wrong. Any better way to achieve this.
Any comments will be appreciated