Help with query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with query

Author

Topic

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-04-28 : 08:25:16

Hi,

I have a stored procedure with a temp table .


DECLARE @Temp table
(
	strLastName varchar(50),
	strFirstName varchar(50),
	strReponse char(10),
	strReponsePourcentage char(10),
	strNomObjet nvarchar(200),
	strDescripteurOrdre char(10),
	strNomDescripteur nvarchar(200),
	strObjetOrdre char(10)
)


SELECT  COUNT(CASE WHEN ((CAST(strReponse as decimal(15,2))) / (CAST(strReponsePourcentage as decimal(15,2))) * 100) >= 65 THEN strReponse ELSE NULL END) AS [65+],
COUNT(CASE WHEN CAST(strReponse as decimal(15,2)) != -1 THEN strReponse ELSE NULL END) AS [number of student],
COUNT(CASE WHEN ((CAST(strReponse as decimal(15,2))) / (CAST(strReponsePourcentage as decimal(15,2))) * 100) >= 85 THEN strReponse ELSE NULL END) AS [85+]
from @Temp
GROUP BY strNomObjet,strNomDescripteur
ORDER BY strNomObjet,strNomDescripteur

The query give me this result
te first row a count how many student did 65% and more
the second is how many student
the third is how many student did 85% and more

31 34 17
24 34 16
30 34 22
25 34 16
0 0 0
29 34 22
28 34 14
22 34 12
0 0 0

Evrething is great but i need to had the % of student who did 65% and more and 85% and more

Ex for the first row : (how many 65% and more / number of student * 100)
31 / 34 * 100 = 91% "That the % for 65% and more
17 / 31 * 100 = 50% "That the % for 85 and more

So it would give something like this :

31 34 17 91 50

Tks in advance!
Luc

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-28 : 08:29:51

[code]Select
[65+],
[number of student],
[85+],
[65% and more]=[65+]*1.0/[number of student]*100,
[85% and more]=[85+]*1.0/[number of student]*100
from

(SELECT COUNT(CASE WHEN ((CAST(strReponse as decimal(15,2))) / (CAST(strReponsePourcentage as decimal(15,2))) * 100) >= 65 THEN strReponse ELSE NULL END) AS [65+],
COUNT(CASE WHEN CAST(strReponse as decimal(15,2)) != -1 THEN strReponse ELSE NULL END) AS [number of student],
COUNT(CASE WHEN ((CAST(strReponse as decimal(15,2))) / (CAST(strReponsePourcentage as decimal(15,2))) * 100) >= 85 THEN strReponse ELSE NULL END) AS [85+]
from @Temp
GROUP BY strNomObjet,strNomDescripteur
ORDER BY strNomObjet,strNomDescripteur)s[/code]

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-04-28 : 08:34:40

Hi tks for the quick reply!

It give me this error :

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

And i forgot to mention it possible that it will be devision by 0
If i remove
ORDER BY strNomObjet,strNomDescripteur
It give me :
Warning: Null value is eliminated by an aggregate or other SET operation.

quote:

Originally posted by sakets_2000

Select 	
	[65+],
	[number of student],
	[85+],
	[65% and more]=[65+]*1.0/[number of student]*100,
	[85% and more]=[85+]*1.0/[number of student]*100
from

	(SELECT  COUNT(CASE WHEN ((CAST(strReponse as decimal(15,2))) / (CAST(strReponsePourcentage as decimal(15,2))) * 100) >= 65 THEN strReponse ELSE NULL END) AS [65+],
	COUNT(CASE WHEN CAST(strReponse as decimal(15,2)) != -1 THEN strReponse ELSE NULL END) AS [number of student],
	COUNT(CASE WHEN ((CAST(strReponse as decimal(15,2))) / (CAST(strReponsePourcentage as decimal(15,2))) * 100) >= 85 THEN strReponse ELSE NULL END) AS [85+]
	from @Temp
	GROUP BY strNomObjet,strNomDescripteur
	ORDER BY strNomObjet,strNomDescripteur)s

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-04-28 : 08:43:19

Hi it working

i remove the order by
and put ISNULL

Tks

Can we use ORder by in subquery??
Is there a way i can use Order by?
Is ok if u use SELECT TOP 99

Select
[65+],
[number of student],
[85+],
[65% and more]=[65+]*1.0/NULLIF([number of student], 0)*100,
[85% and more]=[85+]*1.0/NULLIF([number of student], 0)*100
from

(SELECT COUNT(CASE WHEN ((CAST(strReponse as decimal(15,2))) / (CAST(strReponsePourcentage as decimal(15,2))) * 100) >= 65 THEN strReponse ELSE NULL END) AS [65+],
COUNT(CASE WHEN CAST(strReponse as decimal(15,2)) != -1 THEN strReponse ELSE NULL END) AS [number of student],
COUNT(CASE WHEN ((CAST(strReponse as decimal(15,2))) / (CAST(strReponsePourcentage as decimal(15,2))) * 100) >= 85 THEN strReponse ELSE NULL END) AS [85+]
from @Temp
GROUP BY strNomObjet,strNomDescripteur)s

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-28 : 08:53:31

You can't use order by in sub query. Put it outside if you want it ordered.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-28 : 08:54:53

here,

Select 	
	[65+],
	[number of student],
	[85+],
	[65% and more]=[65+]*1.0/[number of student]*100,
	[85% and more]=[85+]*1.0/[number of student]*100
from

	(SELECT  COUNT(CASE WHEN ((CAST(strReponse as decimal(15,2))) / (CAST(strReponsePourcentage as decimal(15,2))) * 100) >= 65 THEN strReponse ELSE NULL END) AS [65+],
	COUNT(CASE WHEN CAST(strReponse as decimal(15,2)) != -1 THEN strReponse ELSE NULL END) AS [number of student],
	COUNT(CASE WHEN ((CAST(strReponse as decimal(15,2))) / (CAST(strReponsePourcentage as decimal(15,2))) * 100) >= 85 THEN strReponse ELSE NULL END) AS [85+],
	strNomObjet,
	strNomDescripteur
	from @Temp
	GROUP BY strNomObjet,strNomDescripteur
	)s
ORDER BY 
	strNomObjet,strNomDescripteur

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-04-28 : 09:00:28

Tks working great!!!
Have a nice day!

quote:

Originally posted by sakets_2000

here,

Select 	
	[65+],
	[number of student],
	[85+],
	[65% and more]=[65+]*1.0/[number of student]*100,
	[85% and more]=[85+]*1.0/[number of student]*100
from

	(SELECT  COUNT(CASE WHEN ((CAST(strReponse as decimal(15,2))) / (CAST(strReponsePourcentage as decimal(15,2))) * 100) >= 65 THEN strReponse ELSE NULL END) AS [65+],
	COUNT(CASE WHEN CAST(strReponse as decimal(15,2)) != -1 THEN strReponse ELSE NULL END) AS [number of student],
	COUNT(CASE WHEN ((CAST(strReponse as decimal(15,2))) / (CAST(strReponsePourcentage as decimal(15,2))) * 100) >= 85 THEN strReponse ELSE NULL END) AS [85+],
	strNomObjet,
	strNomDescripteur
	from @Temp
	GROUP BY strNomObjet,strNomDescripteur
	)s
ORDER BY 
	strNomObjet,strNomDescripteur

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources