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

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
>
> SQL> select firstname, lastname, count(*) from users;
> select firstname, lastname, count(*) from users
> *
> ERROR at line 1:
> ORA-00937: not a single-group group function
>
> SQL> select firstname, lastname, count(firstname) from users;
> select firstname, lastname, count(firstname) from users
> *
> ERROR at line 1:
> ORA-00937: not a single-group group function
>
> What's the syntax I'm looking for? Also, are there any good SQL
> language
> references on the web? (AltaVista wasn't able to find anything
> useful.)
>
> --
> _/_/_/ Be insatiably curious. Je ne suis fait comme
> aucun
> /_/_/ Ask "why" a lot. de ceux que j'ai vus; j'ose
> croire
> _/_/ n'etre fait comme aucun de ceux qui
> existent.
> / Brian Kendig Si je ne vaux pas mieux, au moins je suis
> autre.
> / bskendig_at_netcom.com --
> Rousseau
> http://home.netscape.com/people/brian/

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;

I hope this helps...

--
Claude
" And were I so tall as to reach the pole,
  Or to grasp the ocea at a span,
  I must be mesured  by my soul.
  The mind is the standard of the man. "
  Dr. Martin Luther King Jr.