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

<cricketunes (AT) yahoo (DOT) com> a écrit dans le message de news: 4e67b349-1ea7-4788-a785-2c62bf12a113...oglegroups.com...
What about doing your homework yourself?

Regards
Michel

On Mar 2, 8:23*pm, cricketu... (AT) yahoo (DOT) com wrote:
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

On Mar 2, 5:23*pm, cricketu... (AT) yahoo (DOT) com wrote:
Despite the prevailing opinion here, let's assume for a moment that
you're not working on a homework assignment (anything this trivial [no
offense] involving students [every professor's favorite entity] is
viewed circumspectly); your query might look something like this:

SELECT
S.STUDENT_NAME,
COUNT(*) CNT
FROM
STUDENT S
WHERE
S.STUDENT_NAME LIKE 'S%'
GROUP BY
S.STUDENT_NAME;

(Note that the above assumes all names are proper-cased.)

Regards,

--Jeff

On Mar 3, 2:40*pm, jefftyzzer <jefftyz... (AT) sbcglobal (DOT) net> wrote:
Now that the cow is out of the barn why not suggest this:

select student_name, count(*) as ct
from students
where instr(upper(student_name), 'S') = 1
group by student_name;

or this:

select student_name, count(*) as ct
from students
where upper(substr(student_name, 1,1)) = 'S'
group by student_name;

or this:

select student_name, count(*) as ct
from students
where substr(student_name, 1,1) not in
('A','B','C','D','E','F','G','H','I','J','K','L',' M','N','O','P'','Q','R','T','U','V','W','X','Y','Z ','a','b','c','d','e','f','g','h','i','j','k','l', 'm','n','o','p','q','r','t','u','v','w','x','y','z ')
group by student_name;

or this:

select distinct student_name, ct
from
(select student_name,
substr(student_name, 1, 1) firstlet,
count(*) over (partition by student_name order by
student_name) as ct
from students)
where upper(firstlet) = 'S';

There are a number of ways to solve a problem.


David Fitzjarrell