I have a SELECT in a stored procedure that is selecting records by
joining records a few tables and doing a GROUP BY on a county column.

I would like to find out if there is an easier way in SQL to get the
count of each GROUP BY as you go.

I know I could create a temp table with an additional column and
iterate through the table updating the new column with the totals for
each county and then to a final SELECT at the end of the temp table,
but if there is an easier way as you are doing the SELECT then I would
like to know it.

here is the pseudo code ..

SELECT
P.Product,
P.ProductNum,
C.County
    <--      Somehow get County of records for that county
FROM
Product P,
County C

WHERE
 P.County_ID = C.County_ID

GROUP BY
  C.County, ...

Is it possible to do it in an "inline" type of way?

Thanks

--
Reid Roman
Future Generation Software
http://www.futuregenerationsoftware.com

Reid

It would be easier if you posted the DDL.  For example is county id in the
product table a foreign key to the county table?  If so you don't need a
join at all.  There probably is a way to do what you want in SQL...but can't
tell with the pseudo code you provided.

Joe

news:3b361fe6$1_2@dnews...

Not sure if this is exactly what you want, but to correct your SQL above it
would be

 SELECT
 P.Product, P.ProductNum, C.County, Count(*)
 FROM Product P, County C
 WHERE
  P.County_ID = C.County_ID
 GROUP BY
    P.Product, P.ProductNum, C.County

The rule of group by is you must ask for an aggregate of some sort (Count,
Sum, etc) and all non-aggregate selected fields must also appear in the
group by. So in this case you will get counts of product numbers in each
county. If ProductNum is unique then this is not what you want, you probably
only want to group by product.
--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
"Bandwagons are like streetcars, there'll be another along in a few
minutes" - Me!

Thanks for the response.
I am posting the project's code to put in in a better context.

I would mention that the project as an overview consists of callboxes
(road side) that call in and post alarms.  Those callboxes are located
in different counties so the report needs to total how many alarms per
county and how many alarms per call box in a date range

My objective again is to get a count of two groups in the select as
they are selected:
1) The first group (or outer group) are all alarm records for each
county.
2) The second group (or inner group) are all the records for a
specific call box.

Here is the actual code : (Slice from a stored procedure ....
@Project_ID , @Start_Date, and @End_Date are input parameters)

 SELECT
   C.Callbox_ID,
   C.Sign_Number,
   C.Ani,
   C.Highway,
   C.LastPrevMaintDate,
   CY.County,
   AL.Alarm_Log_ID,
   AL.Project_ID,
   AL.Alarm_DT,
   AL.Created_DT,
   AL.Last_Called_In_Date,
   AL.Scheduled_Call_In_Date,
   AL.Alarm_Source
  { something like ..
   Count(

 FROM  Callbox   C (NOLOCK),
   County  CY (NOLOCK),
   Alarm_Log AL (NOLOCK)

 WHERE AL.Project_ID   =  @Project_ID
 AND  AL.Alarm_DT  BETWEEN  (@Start_Date)  AND   (@End_Date)
 AND  C.ANI   = AL.ANI
 AND  AL.Alarm_Source =  'E'
 AND  CY.County_ID  =* C.County_ID

 GROUP BY  CY.County,
   C.Sign_Number,
   C.Callbox_ID,
   C.Ani,
   C.Highway,
   C.LastPrevMaintDate,
   AL.Alarm_DT,
   AL.Created_DT,
   AL.Last_Called_In_Date,
   AL.Scheduled_Call_In_Date,
   AL.Alarm_Source,
   AL.Project_ID,
   AL.Alarm_Log_ID

        ORDER BY CY.County, C.Sign_Number

You get a unique key as a composite of two columns using the Alarm_Log
table and the Callbox  with the Project_ID and ANI.

To Wayne's point, to complicate it more here there are some records
where County_ID is NULL.
I have resorted to the temp table approach and iterating through it to
update these values in new columns.  It is not working out to smotthly
right now because of an error that the code is "returning more that
one value in a subquery".

This whole format I believe is a "cross tab" report.  The result will
be using Quick Reports 3.0 and the TQRGrouping functionality to
display it.  The basic grouping is displaying right, all I need are
these totals ..

Thanks

--
Reid Roman
Future Generation Software
http://www.futuregenerationsoftware.com

news:3b36b065$1_2@dnews...

You cannot select both detailed record by record information AND do
groupings at the same time. E.g. if Sign_Number or Highway or any of the
other non-key field is different then that will form a new group.

I don't have enough understanding of your exact needs to suggest the correct
query here, but I think you're looking at at least two different queries.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
"Bandwagons are like streetcars, there'll be another along in a few
minutes" - Me!

Hello,

You are right in that you need two queries.
I re-wrote the stored procedure to create three temp tables.

#MainTempTable
#SignNumbers
#Counties

The main temp table is populated with main SELECT doing the GROUP BY
using County and SignNumber as the first two columns. This main temp
table also has the stats columns added to store the totals using
subsequent queries.

The ORDER BY uses County and SignNumber as well.

The other tables are populated by doing a SELECT DISTINCT for County
and SignNumber values against the Main temp table.

I then iterate through the SignNumber temp table and do updates
against the Main temp table doing a COUNT for the value of that
particular SignNumber column value.

I do the same for the Counties temp table.  The result is a temp table
filled with summary counts for both Counties and SignNumbers.

Using Quick Reports 3.0 I have two TRGroup headers with detail and
Footers on this report and the expression property of the TQRGroup
band set to the two columns of the GROUP BY (County, SignNumber) and
the report works fine grouping and printing the totals.

It would be nice if the Quick Reports  TQRFooter had functionality to
get totals of the records for each TQRGroup band it was associated
with.  Then I believe you could just do the first SELECT in the
process and forget about the summary calculations.

But I did get it working.

Thanks,

 --
Reid Roman
Future Generation Software
http://www.futuregenerationsoftware.com
"Wayne Niddery [TeamB]" <wnidd...@aci.on.ca> wrote in message
news:3b37e1fa_2@dnews...