Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
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

share|improve this question
    
My mssql-server knowledges are aged, but I guess you need a cross join, just double the tables with aliases. –  user65297 Jan 27 '11 at 23:14
    
ok ... have to think there... how to achieve that –  San Jan 28 '11 at 0:11
add comment

1 Answer 1

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.

share|improve this answer
    
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 0: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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.