0
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'

Output

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

Ben Pilbrow
  • 12,041
  • 5
  • 36
  • 57
San
  • 543
  • 4
  • 6

1 Answers1

0

I am assuming the field 'status' is in the 'contract' table. Easiest thing is to create 3 separate queries that you're certain reflect the right number for each total and avoid joins where they're not necessary.

SELECT COUNT(DISTINCT consumerno) as TOTAL FROM consumer LEFT JOIN  contract ON consumerno = cconsumer WHERE status='Active';
SELECT COUNT (DISTINCT cconsumer) as B FROM contract WHERE ccon LIKE '%B%' AND status='Active';
SELECT COUNT (DISTINCT cconsumer) as D FROM contract WHERE ccon LIKE '%D%' AND status='Active';

You can hack up a join on a placeholder field to put them all in one neat row if necessary.

nedm
  • 5,630
  • 5
  • 32
  • 52
  • i have already tried above one but there is a difference of 1000 rows... dont know why... though contract table does not have any blank fields.. – San Jan 28 '11 at 00:10
  • The query you gave in your question includes a join that may be responsible for the difference in the number of rows. Try running the 3 separate queries I gave you above and see if you get the correct answer for each one. – nedm Jan 28 '11 at 21:51
  • The main possibility I see that would explain the difference is if there are 1000 or so consumer records who have no contact entry of either B or D types, AND either the 'status' field is actually in the consumer table or there are other types of contract (with status 'Active') than the 2 you're querying for here. You haven't specified quite enough about your data to know for sure. – nedm Jan 28 '11 at 21:54