Board index » delphi » Returning a SUM and COUNT with GROUP BY

Returning a SUM and COUNT with GROUP BY

Hello,

Please exuse the double post in a few days but countless hours have been
spent trying different things have not worked.  My original post is
entiteled:
"SQL Question - Group By and Sum".

I am lost as to why this does not work:

I am trying to get a Count of Orders per distributor as well as an Order
Total for each distributor. @Start_Date and @End_Date are variables that get
passed to the stored procedure.

As Ignacio suggested, I am using two aliases for the Orders table, one for
the
detail and one for the aggregates.

All  I get is a "1" for the COUNT column "OrderCount" and  get the current
Order_Total column value as the "OrderTotal".

 SELECT
  O2.Order_ID,
  O2.Created_DT,
  O2.Commission_Awarded,
  O2.Product_Total,
  O2.Tax_Total,
  O2.Total_Due,
  O2.Amount_Paid,
  O2.Balance_Due,
  D.Distributor_ID,
  D.First_Name + ' ' + D.Last_Name AS Distributor,
  D.League,
  COUNT(O.Order_ID) AS OrderCount,
  SUM(O.Total_Due) AS OrderTotal

 FROM Orders O2

  INNER JOIN Distributor    D   ON D.Distributor_ID   = O2.Distributor_ID
  INNER JOIN Orders         O  ON O2.Order_ID    = O.Order_ID

 WHERE   O2.Shipped = 1
 AND       UPPER(O2.Status) = 'SHIPPED'
 AND       O2.Created_DT  BETWEEN  (@Start_Date)  AND   (@End_Date )

 GROUP BY
  D.Distributor_ID,
  O2.Order_ID,
  O2.Created_DT,
  O2.Commission_Awarded,
  O2.Product_Total,
  O2.Tax_Total,
  O2.Ship_Total,
  O2.Total_Due,
  O2.Amount_Paid,
  OSD.Final_Ship_Total,
  O2.Balance_Due,
  D.First_Name,
  D.Last_Name,
  D.League

-----
I have tried inserting it into a #temptable (creating two additional columns
for the order count and order totals for each distributor) with the hope of
updating the #Temptable after it has been filled.  But it does not work.

I also tried using a CURSOR in an atttempt to cursor through the #temptable
looking at the Distributor_ID, and can verifiy the varibles in the SQL
de{*word*81} and the values are correct, yet the final select at the end of this
stored procedure shows a "0" in each of the aggregate columns.

Here is that code for that failed attempt using a CURSOR:

DECLARE @ActiveDistID int
DECLARE @OrderCount int

DECLARE TempCursor CURSOR FOR
SELECT Distributor_ID
FROM #TempTable TT <-- The Temptable is created and filled prior to this

OPEN TempCursor

WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM TempCursor into @ActiveDistID

SELECT @OrderCount =

 COUNT(*)
 FROM Orders
 WHERE Distributor_ID = @ActiveDistID

 UPDATE #TempTable
 SET OrderCount = @OrderCount
 WHERE Distributor_ID =@ActiveDistID
 -- I also tried -> WHERE CURRENT OF TempCursor

FETCH NEXT FROM TempCursor into @ActiveDistID
END

CLOSE TempCursor
DEALLOCATE TempCursor

-----

Do you see what is going wrong here? I am using SQL Server 2000.
I really appreciate any help you can offer.

Thanks,

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

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

 

Re:Returning a SUM and COUNT with GROUP BY


Quote
"Reid Roman" <re...@fgsoft.com> wrote in message

news:3eed2f35$1@newsgroups.borland.com...

Quote
> As Ignacio suggested, I am using two aliases for the Orders table, one for
> the
> detail and one for the aggregates.

> All  I get is a "1" for the COUNT column "OrderCount" and  get the current
> Order_Total column value as the "OrderTotal".

Try changing the INNER JOIN between the two aliases to a normal (i.e.,
comma-separated) join, and put the join condition in the WHERE clause.

Cheers,
  Ignacio

Re:Returning a SUM and COUNT with GROUP BY


Quote
"Ignacio Vazquez" <ivazquezATorioncommunications.com> wrote in message

news:3eed3c45$1@newsgroups.borland.com...

Quote
> "Reid Roman" <re...@fgsoft.com> wrote in message
> news:3eed2f35$1@newsgroups.borland.com...
> > As Ignacio suggested, I am using two aliases for the Orders table, one
for
> > the
> > detail and one for the aggregates.

> > All  I get is a "1" for the COUNT column "OrderCount" and  get the
current
> > Order_Total column value as the "OrderTotal".

> Try changing the INNER JOIN between the two aliases to a normal (i.e.,
> comma-separated) join, and put the join condition in the WHERE clause.

I did try to change the INNER JOIN to just JOIN. Can you please illustrate
with the code I posted?  I am not sure what you mean.

Thanks again,

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

Re:Returning a SUM and COUNT with GROUP BY


Quote
"Reid Roman" <re...@fgsoft.com> wrote in message

news:3eed42a3@newsgroups.borland.com...

Quote
> "Ignacio Vazquez" <ivazquezATorioncommunications.com> wrote in message
> news:3eed3c45$1@newsgroups.borland.com...
> > "Reid Roman" <re...@fgsoft.com> wrote in message
> > news:3eed2f35$1@newsgroups.borland.com...
> > > As Ignacio suggested, I am using two aliases for the Orders table, one
> for
> > > the
> > > detail and one for the aggregates.

> > > All  I get is a "1" for the COUNT column "OrderCount" and  get the
> current
> > > Order_Total column value as the "OrderTotal".

> > Try changing the INNER JOIN between the two aliases to a normal (i.e.,
> > comma-separated) join, and put the join condition in the WHERE clause.

> I did try to change the INNER JOIN to just JOIN. Can you please illustrate
> with the code I posted?  I am not sure what you mean.
>>>SELECT
>>>  ...

 FROM Orders O, Orders O2
Quote
>>>  INNER JOIN Distributor    D   ON D.Distributor_ID   = O2.Distributor_ID

>>> WHERE   O2.Shipped = 1

 AND O2.Order_ID=O.Order_ID

Quote
>>> AND       UPPER(O2.Status) = 'SHIPPED'
>>> AND       O2.Created_DT  BETWEEN  (@Start_Date)  AND   (@End_Date )
>>> GROUP BY
>>>  ...

Cheers,
  Ignacio

Re:Returning a SUM and COUNT with GROUP BY


Quote
Reid Roman wrote:

> As Ignacio suggested, I am using two aliases for the Orders table,
> one for the
> detail and one for the aggregates.

> All  I get is a "1" for the COUNT column "OrderCount" and  get the
> current Order_Total column value as the "OrderTotal".

You are getting a count of 1 because of all the other fields you are
selecting. The Group By takes into account all specified fields, so if
total_due is different in each row then the group by will return groups of
one. You cannot ask for an aggregate AND the individual values of that
aggregate in the same query.

Remove all selected fields but the Count, Sum, Distributor_ID and perhaps
League if you want separate groupings on that. You can also remove the
additional join on Orders, there's no need or use in it at all since it's
just returning the exact same fields a second time.

That will get you the correct sum and count values.

--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
Powered by Delphi and IB: http://www.logicfundamentals.com/RadBooks.html
"Democracy, without that guarantee of liberty, is merely a method of
selecting tyrants." - Alan Nitikman

Re:Returning a SUM and COUNT with GROUP BY


"Wayne Niddery [TeamB]" <wnidd...@chaff.aci.on.ca> wrote in message
news:3eedd232$1@newsgroups.borland.com...

Quote
> You are getting a count of 1 because of all the other fields you are
> selecting. The Group By takes into account all specified fields, so if
> total_due is different in each row then the group by will return groups of
> one. You cannot ask for an aggregate AND the individual values of that
> aggregate in the same query.

> Remove all selected fields but the Count, Sum, Distributor_ID and perhaps
> League if you want separate groupings on that. You can also remove the
> additional join on Orders, there's no need or use in it at all since it's
> just returning the exact same fields a second time.

> That will get you the correct sum and count values.

I understand now.  But I still need to return the fields above.

Do you know what is wrong with the CURSOR section?  A solution (if it would
work) is to add the data to a temp table with two additional columns set to
NULL at first, and then use a CURSOR to iterate though the #temptable and
set the values for the COUNT and SUM based on the Distributor_ID.  All done
inside the same stored procedure.

As I said in the original post, the SQL de{*word*81} seems to show the right
values and the UPDATE statement gets called, but when I do a final SELECT
against the #temptable, those two columns meant to hold the aggregate values
are 0.

Really appreciate your help so far and if you can spot what is going wrong
in the CURSOR section I would consider leaving you a thousand of my wives in
my will<g>.

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

Re:Returning a SUM and COUNT with GROUP BY


Quote
Reid Roman wrote:

> I understand now.  But I still need to return the fields above.

> Do you know what is wrong with the CURSOR section?  A solution (if it
> would work) is to add the data to a temp table with two additional
> columns set to NULL at first, and then use a CURSOR to iterate though
> the #temptable and set the values for the COUNT and SUM based on the
> Distributor_ID.  All done inside the same stored procedure.

I would think you would be able to do this, but I'm not really up to speed
on MSSQL stored procs.

The only other way to do this in a single select is to use correlated
sub-queries to get the aggregates, but I'm very sure that would prove to be
very slow, something like:

select <all the fields you want from orders and any joined tables>,
  (select count(*) from orders o2
    where o2Distributor_ID = o.Distributor_ID) as ordercount,
  (select sum(o2.Total_Due) from orders o3
    where o3.Distributor_ID = o.Distributor_ID) as OrderTotal
from orders o

--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
Powered by Delphi and IB: http://www.logicfundamentals.com/RadBooks.html
"Democracy, without that guarantee of liberty, is merely a method of
selecting tyrants." - Alan Nitikman

Re:Returning a SUM and COUNT with GROUP BY


"Wayne Niddery [TeamB]" <wnidd...@chaff.aci.on.ca> wrote in message
news:3eee7a91$1@newsgroups.borland.com...

Quote

> The only other way to do this in a single select is to use correlated
> sub-queries to get the aggregates, but I'm very sure that would prove to
be
> very slow, something like:

> select <all the fields you want from orders and any joined tables>,
>   (select count(*) from orders o2
>     where o2Distributor_ID = o.Distributor_ID) as ordercount,
>   (select sum(o2.Total_Due) from orders o3
>     where o3.Distributor_ID = o.Distributor_ID) as OrderTotal
> from orders o

Thanks Wayne, That did it!
It is not that much slower, not enough to be noticable.

Give me your address, I will end you those wives.<g>

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

Re:Returning a SUM and COUNT with GROUP BY


This can't work. You're grouping by aggregate field, so you're converting it
to plain one-row field value.

You must first run query to get aggregates and join that to distributors.
For this purpose you create a view (or use on-the-fly view, if server
supports it):
create view [owner.]vw_orders_total as select distributor_id,
COUNT(Order_ID) AS OrderCount,
  SUM(Total_Due) AS OrderTotal group by distributor_id

select D.*, OrderCount, OrderTotal from vw_orders_total T join distributor D
on T.distributor_id=D.distributor_id

This will return data for each distributor and its OrderCount, OrderTotal.

--
Robert Cerny
http://codecentral.borland.com/codecentral/ccWeb.exe/author?authorid=...

Quote
"Reid Roman" <re...@fgsoft.com> wrote in message

news:3eed2f35$1@newsgroups.borland.com...
Quote

> Hello,

> Please exuse the double post in a few days but countless hours have been
> spent trying different things have not worked.  My original post is
> entiteled:
> "SQL Question - Group By and Sum".

> I am lost as to why this does not work:

> I am trying to get a Count of Orders per distributor as well as an Order
> Total for each distributor. @Start_Date and @End_Date are variables that
get
> passed to the stored procedure.

> As Ignacio suggested, I am using two aliases for the Orders table, one for
> the
> detail and one for the aggregates.

> All  I get is a "1" for the COUNT column "OrderCount" and  get the current
> Order_Total column value as the "OrderTotal".

>  SELECT
>   O2.Order_ID,
>   O2.Created_DT,
>   O2.Commission_Awarded,
>   O2.Product_Total,
>   O2.Tax_Total,
>   O2.Total_Due,
>   O2.Amount_Paid,
>   O2.Balance_Due,
>   D.Distributor_ID,
>   D.First_Name + ' ' + D.Last_Name AS Distributor,
>   D.League,
>   COUNT(O.Order_ID) AS OrderCount,
>   SUM(O.Total_Due) AS OrderTotal

>  FROM Orders O2

>   INNER JOIN Distributor    D   ON D.Distributor_ID   = O2.Distributor_ID
>   INNER JOIN Orders         O  ON O2.Order_ID    = O.Order_ID

>  WHERE   O2.Shipped = 1
>  AND       UPPER(O2.Status) = 'SHIPPED'
>  AND       O2.Created_DT  BETWEEN  (@Start_Date)  AND   (@End_Date )

>  GROUP BY
>   D.Distributor_ID,
>   O2.Order_ID,
>   O2.Created_DT,
>   O2.Commission_Awarded,
>   O2.Product_Total,
>   O2.Tax_Total,
>   O2.Ship_Total,
>   O2.Total_Due,
>   O2.Amount_Paid,
>   OSD.Final_Ship_Total,
>   O2.Balance_Due,
>   D.First_Name,
>   D.Last_Name,
>   D.League

> -----
> I have tried inserting it into a #temptable (creating two additional
columns
> for the order count and order totals for each distributor) with the hope
of
> updating the #Temptable after it has been filled.  But it does not work.

> I also tried using a CURSOR in an atttempt to cursor through the
#temptable
> looking at the Distributor_ID, and can verifiy the varibles in the SQL
> de{*word*81} and the values are correct, yet the final select at the end of
this
> stored procedure shows a "0" in each of the aggregate columns.

> Here is that code for that failed attempt using a CURSOR:

> DECLARE @ActiveDistID int
> DECLARE @OrderCount int

> DECLARE TempCursor CURSOR FOR
> SELECT Distributor_ID
> FROM #TempTable TT <-- The Temptable is created and filled prior to this

> OPEN TempCursor

> WHILE @@FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM TempCursor into @ActiveDistID

> SELECT @OrderCount =

>  COUNT(*)
>  FROM Orders
>  WHERE Distributor_ID = @ActiveDistID

>  UPDATE #TempTable
>  SET OrderCount = @OrderCount
>  WHERE Distributor_ID =@ActiveDistID
>  -- I also tried -> WHERE CURRENT OF TempCursor

> FETCH NEXT FROM TempCursor into @ActiveDistID
> END

> CLOSE TempCursor
> DEALLOCATE TempCursor

> -----

> Do you see what is going wrong here? I am using SQL Server 2000.
> I really appreciate any help you can offer.

> Thanks,

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

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

Re:Returning a SUM and COUNT with GROUP BY


Quote
"Robert Cerny" <robert.qwe.ce...@neosys.xrs.qwe.si> wrote in message

news:bcqm7v.fbg.1@neosys.xrs.si...

Quote
> This can't work. You're grouping by aggregate field, so you're converting
it
> to plain one-row field value.

> You must first run query to get aggregates and join that to distributors.
> For this purpose you create a view (or use on-the-fly view, if server
> supports it):
> create view [owner.]vw_orders_total as select distributor_id,
> COUNT(Order_ID) AS OrderCount,
>   SUM(Total_Due) AS OrderTotal group by distributor_id

> select D.*, OrderCount, OrderTotal from vw_orders_total T join distributor
D
> on T.distributor_id=D.distributor_id

> This will return data for each distributor and its OrderCount, OrderTotal.

Thanks for your advice.

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

Re:Returning a SUM and COUNT with GROUP BY


Quote
"Reid Roman" <re...@fgsoft.com> wrote in message

news:3ef13e71$1@newsgroups.borland.com...

Other Threads