Re: How do I write a "tally" query in SQL?

Claude-Sébastien Jean wrote:
>
> Brian Kendig wrote:
>
> > Now that I'm embarassed for not having remembered how to do the simple
> > case, let me further embarass myself by asking how to do a slightly
> > more complex case. Say the initial table looks like this:
> >
> > Firstname Lastname Order
> > --------- -------- -----
> > JOHN SMITH 10001
> > JOHN SMITH 10002
> > JOHN SMITH 10003
> > TIM JONES 10004
> > TIM JONES 10005
> > TIM JONES 10006
> > TIM JONES 10007
> > JAMES DOE 10008
> > JAMES DOE 10009
> >
> > I want to generate this output:
> >
> > Firstname Lastname Tally
> > --------- -------- -----
> > JOHN SMITH 3
> > TIM JONES 4
> > JAMES DOE 2
> >
> > I don't understand the ORA-973 error I'm getting, and I have no
> > reference material to look it up in:
> >
> > SQL> select firstname, lastname, count(lastname) from users;
> > select firstname, lastname, count(lastname) from users
> > *
> > ERROR at line 1:
> > ORA-00937: not a single-group group function
> >...
>
> You want to group your information by Firstname, Lastname
> So your query should be:
> select firstname, lastname, count(firstname) from users
> group by firstname, lastname;

Yes. The count() function only works on groups and you did not specify a group by in any of the sample queries you gave. The answer c-s j gave is correct for your sample table. The query against a properly normalized set of tables would only be slightly different:

SQL> select e.firstname, e.lastname, count(o.emp_id) tally
SQL> from orders o, emp e
SQL> where o.emp_id = e.emp_id
SQL> group by o.emp_id, e.firstname, e.lastname;

FIRSTNAME            LASTNAME                  TALLY
-------------------- -------------------- ----------
JOHN                 SMITH                         3
TIM                  JONES                         4
JAMES                DOE                           2

Even though firstname and lastname only form "groups" of one, they must still be included in the group by clause.

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"