Re: simple query to view matching record and count

From: ddf <oratune_at_msn.com>
Date: Wed, 3 Mar 2010 12:46:44 -0800 (PST)
Message-ID: <337a1490-e8f5-4648-b63f-fdeacf27f995_at_p3g2000pra.googlegroups.com>

On Mar 3, 2:40 pm, jefftyzzer <jefftyz..._at_sbcglobal.net> wrote:
> On Mar 2, 5: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
>
> 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 Received on Wed Mar 03 2010 - 14:46:44 CST