# 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)

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
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

Original text of this message