multiple count problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

multiple count problem

Author

Topic

ajkush
Starting Member

1 Post

Posted - 2010-01-14 : 02:31:35

hi,
i have two tables.

i want to create report which display

claimid, client name , no of total cases , no of closed cases , no of open cases,

the tables are

first table :-

CREATE TABLE `claim_info` (
`sr_no` int(255) NOT NULL auto_increment,
`name` varchar(255) default NULL,
`clientid` varchar(255) default NULL,
`title` varchar(255) default NULL,
`organisation` varchar(255) default NULL,
`address` varchar(255) default NULL,
`city` varchar(255) default NULL,
`client_state` varchar(255) default NULL,
`zip_code` varchar(255) default NULL,
`country` varchar(255) default NULL,
`work_phone` varchar(255) default NULL,
`home_phone` varchar(255) default NULL,
`fax` varchar(255) default NULL,
`email` varchar(255) default NULL,
`ur_web` varchar(255) default NULL,
`Amount_of_claim` varchar(255) default NULL,
`type_of_org` varchar(255) default NULL,
`provisional_instruct` varchar(255) default NULL,
`basis_of_claim` varchar(255) default NULL,
`age_of_claim` varchar(255) default NULL,
`remarks` varchar(255) default NULL,
`debt_name` varchar(255) default NULL,
`debt_title` varchar(255) default NULL,
`debt_name_of_organisation` varchar(255) default NULL,
`debt_address` varchar(255) default NULL,
`debt_country` varchar(255) default NULL,
`debt_state_of_province` varchar(255) default NULL,
`debt_city` varchar(255) default NULL,
`debt_zipcode` varchar(255) default NULL,
`debt_work_phone` varchar(255) default NULL,
`debt_home_phone` varchar(255) default NULL,
`debt_fax` varchar(255) default NULL,
`debt_email` varchar(255) default NULL,
`debt_url` varchar(255) default NULL,
`client_name` varchar(255) default NULL,
`client_title` varchar(255) default NULL,
`client_name_of_org` varchar(255) default NULL,
`client_address` varchar(255) default NULL,
`client_countary` varchar(255) default NULL,
`state` varchar(255) default NULL,
`client_city` varchar(255) default NULL,
`client_zip_code` varchar(255) default NULL,
`client_work_phone` varchar(255) default NULL,
`client_home_phone` varchar(255) default NULL,
`client_fax` varchar(255) default NULL,
`client_email` varchar(255) default NULL,
`client_url` varchar(255) default NULL,
`status` varchar(255) default NULL,
`date_of_subm` date default NULL,
`claims_ref_no` varchar(255) default NULL,
`foreclosureremarks` varchar(2000) default NULL,
`date_of_foreclosure` varchar(255) default NULL,
`assign_status` varchar(255) default NULL,
`mode_recovery` varchar(255) default NULL,
`claim_settled` varchar(50) default NULL,
`currency` varchar(50) default NULL,
`rec_currency` varchar(50) default NULL,
PRIMARY KEY (`sr_no`),
UNIQUE KEY `claims_ref_no` (`claims_ref_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `claim_info` */

insert into `claim_info`(`sr_no`,`name`,`clientid`,`title`,`organisation`,`address`,`city`,`client_state`,`zip_code`,`country`,`work_phone`,`home_phone`,`fax`,`email`,`ur_web`,`Amount_of_claim`,`type_of_org`,`provisional_instruct`,`basis_of_claim`,`age_of_claim`,`remarks`,`debt_name`,`debt_title`,`debt_name_of_organisation`,`debt_address`,`debt_country`,`debt_state_of_province`,`debt_city`,`debt_zipcode`,`debt_work_phone`,`debt_home_phone`,`debt_fax`,`debt_email`,`debt_url`,`client_name`,`client_title`,`client_name_of_org`,`client_address`,`client_countary`,`state`,`client_city`,`client_zip_code`,`client_work_phone`,`client_home_phone`,`client_fax`,`client_email`,`client_url`,`status`,`date_of_subm`,`claims_ref_no`,`foreclosureremarks`,`date_of_foreclosure`,`assign_status`,`mode_recovery`,`claim_settled`,`currency`,`rec_currency`) values (1,'Ajay Sharma','c_Aj_113','Web Developer','IDR','Address','','','','','5566448899','','','technical@indiadebtrecovery.com','','233000','Individual','Amicable Negotiation,Investigate and Advise,','Invoice,Contract,','Less than 12 months','none','Munna','Employee','XYZ','12 street.','','','','','','','','test@test.com','','','','','','','','','','','','','','','open','2009-11-22','c_Aj_113/1/2009',NULL,NULL,'assigned',NULL,NULL,'Rs',NULL),(2,'Ajay Sharma','c_Aj_113','Web Developer','IDR','Address','','','','','5566448899','','','technical@indiadebtrecovery.com','','233000','Individual','Investigate and Advise,File Suit Immediately,','Invoice,Contract,','Less than 12 months','none','tunna','Employee','ABC','12 street.','','','','','','','','test@test.com','','','','','','','','','','','','','','','open','2009-11-22','c_Aj_113/2/2009',NULL,NULL,'unassigned',NULL,NULL,'Rs',NULL),(3,'Sumit Soman','c_Su_114','Web Developer','IDR','Address','','','','','','','','trainee@indiadebtrecovery.com','','43000','Individual','Amicable Negotiation,Investigate and Advise,','Advance,','Less than 6 months','dfg','Lunna','TiTle','JKL','','','','delhi','','','','','test2@test.com','','','','','','','','','','','','','','','open','2009-11-22','c_Su_114/1/2009',NULL,NULL,'assigned',NULL,'200000','Pound(Britain GBB)','Pound(Britain GBB)'),(4,'Sumit Soman','c_Su_114','Web Developer','IDR','Address','','','','','','','','trainee@indiadebtrecovery.com','','50000','Individual','Amicable Negotiation,Investigate and Advise,','Advance,','Less than 6 months','dfg','Punna','TiTle','DGF','','','','delhi','','','','','test3@test.com','','','','','','','','','','','','','','','open','2009-11-22','c_Su_114/2/2009',NULL,NULL,'unassigned',NULL,NULL,'Euro',NULL);

second table :-

CREATE TABLE `lawyerprofile` (
`lawyerid` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`firmname` varchar(255) default NULL,
`officeaddress` varchar(255) default NULL,
`city` varchar(255) NOT NULL default '',
`state` varchar(255) default NULL,
`country` varchar(255) default NULL,
`email` varchar(255) NOT NULL,
`phone` varchar(180) default NULL,
`website` varchar(255) default NULL,
`yearofest` varchar(250) default NULL,
`joindate` varchar(255) default NULL,
`typeoforg` varchar(255) default NULL,
`nameprop` varchar(255) default NULL,
`addprop` varchar(255) default NULL,
`ageprop` varchar(255) default NULL,
`qualprop` varchar(255) default NULL,
`expprop` varchar(255) default NULL,
`nooflaw` int(8) default NULL,
`noofothremp` int(8) default NULL,
`areaofspec` varchar(255) default NULL,
`listofclient` varchar(255) default NULL,
`status` varchar(255) default NULL,
PRIMARY KEY (`lawyerid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `lawyerprofile` */

insert into `lawyerprofile`(`lawyerid`,`name`,`firmname`,`officeaddress`,`city`,`state`,`country`,`email`,`phone`,`website`,`yearofest`,`joindate`,`typeoforg`,`nameprop`,`addprop`,`ageprop`,`qualprop`,`expprop`,`nooflaw`,`noofothremp`,`areaofspec`,`listofclient`,`status`) values ('l_La_0','Lakhvinder','IDR2','356 SCO Sec 266','Chandigarh','UT','India','Lak@inditrecovery.com','5566448899','','2009/07/07','22/12/09','Sole Proprietorship','Lakhvinder','Home Address','26','LLb','5 years',12,12,'none','none','Determined Lawyer'),('l_Ni_1','Nikhil','IDR','356 SCO Sec 266','chd','UT','India','Nl@indiacovery.com','5566448899','','2009/07/07','22/12/09','Sole Proprietorship','Lakhvinder','Home Address','26','LLb','5 years',12,10,'none','none','Determined Lawyer');

i am using the query

:---

SELECT DISTINCT assign_claim.claimid,claim_info.name,COUNT(claimid) AS total,(SELECT COUNT(claimid) FROM assign_claim WHERE STATUS='open') AS OPEN,(SELECT COUNT(claimid) FROM assign_claim WHERE STATUS='closed') AS closed FROM assign_claim LEFT JOIN claim_info ON (assign_claim.claimid=claim_info.claims_ref_no) where lawyerid='l_La_0' GROUP BY(claimid)

but it giving wrong result:-

S.No Claim Id Client Name Total Files Open Closed
1 . c_Aj_113/1/2009 Ajay Sharma 3 3 2
2 . c_Su_114/1/2009 Sumit Soman 2 3 2

i want result: -

S.No Claim Id Client Name Total Files Open Closed
1 . c_Aj_113/1/2009 Ajay Sharma 3 1 2
2 . c_Su_114/1/2009 Sumit Soman 2 2 0

i um not able to find how to count the closed and open files with claimid.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 02:38:22

[code]select c.claim_id,
c.name as [client name],
a.total as [no of total cases],
a.closed as [no of closed cases],
a.open as [no of open cases]
from claim_info c
inner join (select claim_id,
count(*) as total,
count(case when status='open' then claim_id else null end) as open,
count(case when status='closed' then claim_id else null end) as closed
from assign_claim
group by claim_id)a
on a.claim_id=c.claim_id
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources