Re: SQL to count rows in Goup By query?

In article <1994May30.235803.6724_at_zed.com>, bachn_at_diamond.zed.com (Bach Nguyen) wrote:

> Hi
>
> Is it possible to write a single sql to get the number of rows returned
> in a Select query containing a Group By clause?
>
> ex: workorder
> =========
>
> workorderID priority
> 1 M
> 2 L
> 3 L
> 4 H
> 5 M
> 6 L
>
>
> sql1) SELECT priority, count(priority) from workorder GROUP BY priority;
>
> res1) priority count(priority)
> M 2
> L 3
> H 1
>
>
> total rows return: 3 rows.
>
>
> I know in Access, we can do something like creating 2 queries.
> Second one running a count base on the first one.
>
> query1: SELECT priority from workorder group by priority;
> query2: SELECT COUNT(priority) from query1;
>
> thanks in advance
>
> Bach
> --
>
> --------------------------------------------------------------------------
> |Internet: aruba!bachn_at_uu2.psi.com
> Bach Nguyen |UUCP : ...!uunet!uupsi2!aruba!bachn
> Club Zed |
> --------------------------------------------------------------------------

in oracle, you can do this using:

   select count(count(priority)) from workorder GROUP BY priority;

however, this is highly non-standard and i don't think it's documented.

the above kludge works with things like max, but given that you are just doing a count, a more standard and easier way to get the same answer is:

    select count(distinct priority) from workorder;

you can also use an approach similar to the one you describe, by defining a view:

   create view t as
   SELECT priority from workorder group by priority;

   select count(*) from t;

this approach is too clumsy for this particular case, but might make sense in some contexts. Received on Mon Jun 06 1994 - 23:54:05 CEST