Re: simple query to view matching record and count

From: ddf <oratune_at_msn.com>
Date: Wed, 3 Mar 2010 08:32:45 -0800 (PST)
Message-ID: <a5d4cb62-df5e-4672-88c4-72ed603104a9_at_k36g2000prb.googlegroups.com>

On Mar 2, 8:23 pm, cricketu..._at_yahoo.com wrote:
> I have the following table
>
> Student:marks
> Steve:90
> Sam:85
> Sue:95
> Mark:75
> Steve:100
> Mark:81
> Sue:92
> Sue:94
>
> What query would provide me a list of all students with names
> beginning with S and the number of records they are present in?
>
> i.e
>
> Steve 2
> Sam 1
> Sue 3
>
> Thanks,
> C

We can do this the hard way:

with students as (

	select
		'Steve' name,'90' grade
	from dual
	union
	select
		'Sam','85'
	from dual
	union
	select
		'Sue','95'
	from dual
	union
	select
		'Mark','75'
	from dual
	union
	select
		'Steve','100'
	from dual
	union
	select
		'Mark','81'
	from dual
	union
	select
		'Sue','92'
	from dual
	union
	select
		'Sue','94'
	from dual

)
select c.name, count(c.CapS)
from
(select

	name,
	case when substr(name, 1, 1) = chr(65) then 1
             when substr(name, 1, 1) = chr(66) then 2
             when substr(name, 1, 1) = chr(67) then 3
             when substr(name, 1, 1) = chr(68) then 4
             when substr(name, 1, 1) = chr(69) then 5
             when substr(name, 1, 1) = chr(70) then 6
             when substr(name, 1, 1) = chr(71) then 7
             when substr(name, 1, 1) = chr(72) then 8
             when substr(name, 1, 1) = chr(73) then 9
             when substr(name, 1, 1) = chr(74) then 10
             when substr(name, 1, 1) = chr(75) then 11
             when substr(name, 1, 1) = chr(76) then 12
             when substr(name, 1, 1) = chr(77) then 13
             when substr(name, 1, 1) = chr(78) then 14
             when substr(name, 1, 1) = chr(79) then 15
             when substr(name, 1, 1) = chr(80) then 16
             when substr(name, 1, 1) = chr(81) then 17
             when substr(name, 1, 1) = chr(82) then 18
             when substr(name, 1, 1) = chr(83) then 19
             when substr(name, 1, 1) = chr(84) then 20
             when substr(name, 1, 1) = chr(85) then 21
             when substr(name, 1, 1) = chr(86) then 22
             when substr(name, 1, 1) = chr(87) then 23
             when substr(name, 1, 1) = chr(88) then 24
             when substr(name, 1, 1) = chr(89) then 25
             when substr(name, 1, 1) = chr(90) then 26
	     else 0
	end CapS
from
	students) c

where c.CapS = 19
group by c.name;

and I'm sure your instructor would be impressed that a professional DBA was able to make your assignment much more complicated than it should be. I suggest you try solving this on your own using methods your instructor has already covered in class. The desired solution is MUCH simpler than posted here.

David Fitzjarrell Received on Wed Mar 03 2010 - 10:32:45 CST