How To Convert This Query in Sql Server

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

How To Convert This Query in Sql Server

Author

Topic

rohan.rishi.2007
Starting Member

8 Posts

Posted - 2010-09-18 : 08:40:52

Hello to all
i used M.S.Access but now i hv 2 shift in sql server 2005.
but i m facing sum problem with this query please help me guys.

The query is

Select c.classname,c.classshort,c.classno,c.class , Sum(1) AS mTT , Sum(IIf(st.sex=1,1,0)) AS mTF , Sum(IIf(st.sex=1,0,1)) AS mTM from Stud0809 as st,Class as c where st.status =0 and st.classno = c.classno and st.Sessionno=2 Group by c.classname,c.classshort,c.classno,c.class Order by c.class,c.classname,c.classshort,c.classno

how can i convert this query in sql server 2005

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-18 : 08:53:14

try this:



Select 
c.classname
,c.classshort
,c.classno
,c.class
,Sum(1) AS mTT 
,sum(case when st.sex = 1 then 1 else 0 end) as mTF
,sum(case when st.sex = 1 then 0 else 1 end) as mTM
--,Sum(IIf(st.sex=1,1,0)) AS mTF
--,Sum(IIf(st.sex=1,0,1)) AS mTM 

from Stud0809 as st
join Class as c 
on st.classno = c.classno 
where 
	st.status =0 
and st.Sessionno=2 

Group by 
	c.classname
	,c.classshort
	,c.classno
	,c.class 

Order by 
	c.class
	,c.classname
	,c.classshort
	,c.classno

p.s.: I don't see the point/meaning between:
,Sum(IIf(st.sex=1,1,0)) AS mTF
,Sum(IIf(st.sex=1,0,1)) AS mTM
:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-18 : 08:58:11

thats just for calculating sum of males and females isnt it? he's adding up males in one case and females in second

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-19 : 06:58:21

visakh, that's what i assumed.
i would "hardcode" both case statements with real values:

sum(case when st.sex = 1 then 1 else 0 end) as Male
sum(case when st.sex = 2 then 1 else 0 end) as Female
sum(case when st.sex not in (1,2) then 1 else 0 end) as N/A_error

where 1 is for Male and 2 is for Female - only to avoid any NULL values or any other values that might appear in this column (100, -10, 99, 0, NULL, etc.) if there is no constraint set on this column and would get summed up automatically in the false category.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-19 : 11:29:14

hmm, fair enough

thats much better way to go

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-19 : 12:20:22

:)
well, if you are statistician, then you must be prepared for all nonsense :)

rohan.rishi.2007
Starting Member

8 Posts

Posted - 2010-09-21 : 10:32:23

Thank guys .....it rally help be in touch with me
bcoz .....i thought this is going really tough for me.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources