query problem urgenttttttttt - 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

query problem urgenttttttttt

Author

Topic

pkuchaliya
Starting Member

49 Posts

Posted - 2008-07-18 : 03:55:58

hi to all,
Please Help me , I have three table given below

CREATE TABLE [dbo].[nk_user_main_info_](

[profile_id] [varchar](50) ,
[profile_first_name] [varchar](50) ,
[education_detail_status] [bit] NULL,
[experience_status] [bit] NULL
)

insert into [nk_user_main_info_] values ('abc001','ramsing','1','1')
insert into [nk_user_main_info_] values ('abc002','manun','1','1')
insert into [nk_user_main_info_] values ('abc003','sanjeet','1','1')
insert into [nk_user_main_info_] values ('abc004','sam','1','1')
insert into [nk_user_main_info_] values ('abc005','piku','1','1')

create table nk_user_resume_summery_([profile_id] [varchar](50) ,
experience_year int,
area varchar(50))

insert into nk_user_resume_summery_ values ('abc001','0','engineer')
insert into nk_user_resume_summery_ values ('abc002','0','developer')
insert into nk_user_resume_summery_ values ('abc003','3','quality assurance')
insert into nk_user_resume_summery_ values ('abc004','5','tester')
insert into nk_user_resume_summery_ values ('abc005','0','software head')

create table nk_user_education_info_([profile_id] [varchar](50) ,
graduation_status bit,
post_graduation_status bit)

insert into nk_user_education_info_ values ('abc001','1','0')
insert into nk_user_education_info_ values ('abc002','1','0')
insert into nk_user_education_info_ values ('abc003','1','0')
insert into nk_user_education_info_ values ('abc004','1','1')
insert into nk_user_education_info_ values ('abc005','1','1')
------------------------------------

problem is that i have to find total no of record who are graduate and post graduate having experience is zero. i make the three query and union them. but i dont know how to find total number of record .

pankaj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 04:15:20

select sum(case when t2.graduation_status=1 and t1.experience_year=0 then 1 else 0 end)as graduatecount,
sum(case when t2.post_graduation_status=1 and t1.experience_year=0 then 1 else 0 end)as postgraduatecount
from nk_user_resume_summery_ t1
join nk_user_education_info_ t2
on t2.profile_id=t1.profile_id
join nk_user_main_info_ t3
on t3.profile_id=t1.profile_id

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-07-18 : 17:27:06

You can also do it this way.


SELECT SUM(Cast(graduation_status AS INT)) AS Graduatecount
     , SUM(cast(post_graduation_status AS INT)) AS PostGraduateCount 
FROM nk_user_education_info_ UEI 
	INNER JOIN nk_user_resume_summery_ URS ON UEI.profile_id = URS.profile_id
WHERE (UEI.graduation_status = 1 OR UEI.post_graduation_status = 1) 
	AND URS.experience_year = 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources