Path: news.cambrium.nl!textnews.cambrium.nl!feeder3.cambriumusenet.nl!feed.tweaknews.nl!postnews.google.com!p3g2000pra.googlegroups.com!not-for-mail
From: ddf <oratune@msn.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: simple query to view matching record and count
Date: Wed, 3 Mar 2010 12:46:44 -0800 (PST)
Organization: http://groups.google.com
Lines: 93
Message-ID: <337a1490-e8f5-4648-b63f-fdeacf27f995@p3g2000pra.googlegroups.com>
References: <4e67b349-1ea7-4788-a785-2c62bf12a113@e7g2000yqf.googlegroups.com> 
 <b7e6d5d1-cb9c-4807-a970-577ff3ef59b2@v20g2000yqv.googlegroups.com>
NNTP-Posting-Host: 72.192.72.65
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1267649204 32051 127.0.0.1 (3 Mar 2010 20:46:44 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 3 Mar 2010 20:46:44 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: p3g2000pra.googlegroups.com; posting-host=72.192.72.65; 
 posting-account=KXUmygkAAABvBFmgDBe4RBLFwhTRAMZC
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; 
 InfoPath.2; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; 
 .NET CLR 3.5.30729),gzip(gfe),gzip(gfe)
Xref:  news.cambrium.nl

On Mar 3, 2:40=A0pm, jefftyzzer <jefftyz...@sbcglobal.net> wrote:
> On Mar 2, 5:23=A0pm, cricketu...@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
> =A0 =A0 =A0 =A0 S.STUDENT_NAME,
> =A0 =A0 =A0 =A0 COUNT(*) CNT
> FROM
> =A0 =A0 =A0 =A0 STUDENT S
> WHERE
> =A0 =A0 =A0 =A0 S.STUDENT_NAME LIKE 'S%'
> GROUP BY
> =A0 =A0 =A0 =A0 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') =3D 1
group by student_name;

or this:

select student_name, count(*) as ct
from students
where upper(substr(student_name, 1,1)) =3D '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) =3D 'S';

There are a number of ways to solve a problem.


David Fitzjarrell