Multi Column grouping

22 Jun 2006 8:56 PM

Scott Cadreau

I have a table with 9 code columns in it. I want a listing of every
possible code in any of the 9 columns with a count of each. Is there a way
to do this without creating a new table that has 9x the rows that the
current table has or without 9 queries plus a sumation query?

The two ways I can get the correct number now are more time consuming that I
would like because they both require "running the table" a number of times
and the table is very large (20MM+ rows).

Thanks,

Scott

22 Jun 2006 9:08 PM

Arnie Rowland

GROUP BY, of course

SELECT
     Col1
   , Col2
   , Col3
   , etc.
   , count(1)
FROM MyTable
GROUP BY
     Col1
   , Col2
   , Col3
   , etc.

This will provide a row (and its count) for each distinct combination of codes.

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Show quote

"Scott Cadreau" <scadr***@aros.net> wrote in message news:2uDmg.289$Mz3.260@fed1read07...
>I have a table with 9 code columns in it. I want a listing of every
> possible code in any of the 9 columns with a count of each. Is there a way
> to do this without creating a new table that has 9x the rows that the
> current table has or without 9 queries plus a sumation query?
>
> The two ways I can get the correct number now are more time consuming that I
> would like because they both require "running the table" a number of times
> and the table is very large (20MM+ rows).
>
> Thanks,
>
> Scott
>
>