simple query to view matching record and count [Oracle]

Prev: Query with LONG RAW
Next: The penny hasn't dropped yet...

From: cricketunes on 2 Mar 2010 20:23

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

From: Michel Cadot on 3 Mar 2010 00:14

From: ddf on 3 Mar 2010 11:32

On Mar 2, 8:23 pm, cricketu...(a)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

From: jefftyzzer on 3 Mar 2010 14:40

On Mar 2, 5:23 pm, cricketu...(a)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

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

From: ddf on 3 Mar 2010 15:46

On Mar 3, 2:40 pm, jefftyzzer <jefftyz...(a)sbcglobal.net> wrote:
> On Mar 2, 5:23 pm, cricketu...(a)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
>
> 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- Hide quoted text -
>
> - Show quoted text -

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

|
Pages: 1
Prev: Query with LONG RAW
Next: The penny hasn't dropped yet...