Group summary totals Help please

Hi,

I am pretty new to Crystal 8.5 and am having problems using formulas on a group summary field.

The database is > 1 million records and I am trying to group a field (for quicker report generation). The field is the time (in seconds) numeric and I want to be able to manipulate this field and get it into hhhh:mm:ss format.

In this application, I want to add up all the seconds for each person in a department, then give a total for each person however in hh:mm:ss format.

Eg Person Table

Name Duration(in seconds)

Peter 50
John 10
Peter 45
John 22
John 80
Peter 100

The Crystal generated SQL will generate

Peter 195
John 112

This is straight forward, however, I now need to convert this to hh:mm:ss format to give me

Peter 00:03:15
John 00:01:52

I can do the above if I look at each individual record (ie no grouping) and use formulas on the Duratin field, however, because of the size of the database, I want to group the records and then manipulate the SUM (Person."Duration") field.

Does anybody have a solution?

(I hope my explanation of my problem is clear ...)

Thanking you in advance.

TheodoreOK Guys,

Solved the problem.

Thanks anyway.

Theodore|||Theo,

I have similar problem, any chance of posting the solution

Regards

Steve Dyson|||Why do you not just use the same formula you used for individual records, to convert the summary? The field would be sum of duration.|||My problem is that i have the following:

Planned and unplanned items.

I can group and Sum them that is the easy bit.

Because this is going to be a sub report, i need these totals to appear in the Page header as i only want to see the Totals

Steve

Group By??

Hi,

I have a pretty simple problem for a SQL programmer regarding GROUPINGS:

I have two tables tblShoppingCarts and tblProducts.
The first table stores all shopping cart data.
I would like to retrieve all this data (cart and all related product
information) BUT need to GROUP BY tblShoppingCarts.sessionid.

Also, any suggestions on fields to include in the shopping cart would be
much appreciated.

Thanks for your help.

NOTE: To retreive all cart information run the stored procedure EXEC
spGetShoppingCarts NULL, NULL

--------------

CREATE TABLE [dbo].[tblProducts] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[category] [int] NOT NULL ,
[publishdate] [smalldatetime] NOT NULL ,
[title] [varchar] (100) NOT NULL ,
[shorttitle] [varchar] (30) NULL ,
[version] [float] NOT NULL ,
[build] [int] NOT NULL ,
[shortDescription] [varchar] (1000) NULL ,
[description] [varchar] (4000) NOT NULL ,
[retailPrice] [money] NOT NULL ,
[oemPrice] [money] NULL ,
[hasImage] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblShoppingCarts] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[sessionid] [bigint] NOT NULL ,
[publishdate] [smalldatetime] NOT NULL ,
[modifieddate] [smalldatetime] NULL ,
[productid] [int] NOT NULL ,
[quantity] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE PROCEDURE spGetShoppingCarts

@.id int = NULL,
@.sessionid bigint = NULL

AS

-- Get all shopping carts
IF (@.id IS NULL) AND (@.sessionid IS NULL)

BEGIN

SELECT
sc.[id],
sc.sessionid,
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
COUNT(sc.sessionid) AS ProductCount
--p.discount,
--p.quantity AS DiscountQuantity,
--p.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
GROUP BY
sc.[id],
--I WANT TO GROUP BY SESSIONID!!!!!!!!!!!!!!!!!! WHY DOESNT THIS
WORK??
sc.sessionid,
sc.publishdate,
sc.modifieddate,
sc.productid,
p.title,
p.shorttitle,
sc.quantity

ORDER BY
sc.[modifieddate] DESC

END

ELSE

BEGIN

-- Get shopping cart by session
IF (@.id IS NULL)

BEGIN

SELECT
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
ProductCount = ( SELECT COUNT(sc.productid) FROM tblShoppingCarts sc
WHERE sc.sessionid = @.sessionid )
--p.discount,
--p.quantity AS DiscountQuantity,
--p.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
AND sc.[sessionid] = @.sessionid

END

-- Get shopping cart by id
ELSE

BEGIN

SELECT
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
COUNT(sc.productid) AS ProductCount
--p.discount,
--p.quantity AS DiscountQuantity,
--p.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
AND sc.[id] = @.id

GROUP BY
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.title,
p.shorttitle,
sc.quantity
END

END
GOThanks everyone for your help!

I finally worked out after a bit of reading exactly how the GROUP BY
statement works and why I would only use it if I where aggregating
information (ie SUM, COUNT etc).
The solution for the stored procedure was:

CREATE PROCEDURE spGetShoppingCarts

@.id int = NULL,
@.sessionid bigint = NULL

AS

-- Get all shopping carts
IF (@.id IS NULL) AND (@.sessionid IS NULL)

BEGIN

SELECT
sc.sessionid,
LastUpdated = MAX( sc.publishdate ),
TotalPrice = SUM(sc.quantity * p.retailPrice)
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
GROUP BY
sc.sessionid
ORDER BY
2 DESC

END

ELSE

BEGIN

-- Get shopping cart by session
IF (@.id IS NULL)

BEGIN

SELECT
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
TotalPrice = (sc.quantity * p.retailPrice),
sc.discount,
sc.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
AND sc.[sessionid] = @.sessionid

END

-- Get shopping cart by id
ELSE

BEGIN

SELECT
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
TotalPrice = (sc.quantity * p.retailPrice),
sc.discount,
sc.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
AND sc.[id] = @.id

GROUP BY
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.title,
p.shorttitle,
sc.quantity,
sc.discount,
sc.code ,
p.retailPrice
END

END
GO

"Stephen McCormack" <stephenm@.mwebsolutions.com.au> wrote in message
news:XdOMa.235$JI4.5088@.news-server.bigpond.net.au...
> Hi,
> I have a pretty simple problem for a SQL programmer regarding GROUPINGS:
> I have two tables tblShoppingCarts and tblProducts.
> The first table stores all shopping cart data.
> I would like to retrieve all this data (cart and all related product
> information) BUT need to GROUP BY tblShoppingCarts.sessionid.
> Also, any suggestions on fields to include in the shopping cart would be
> much appreciated.
> Thanks for your help.
> NOTE: To retreive all cart information run the stored procedure EXEC
> spGetShoppingCarts NULL, NULL
> --------------
> CREATE TABLE [dbo].[tblProducts] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [category] [int] NOT NULL ,
> [publishdate] [smalldatetime] NOT NULL ,
> [title] [varchar] (100) NOT NULL ,
> [shorttitle] [varchar] (30) NULL ,
> [version] [float] NOT NULL ,
> [build] [int] NOT NULL ,
> [shortDescription] [varchar] (1000) NULL ,
> [description] [varchar] (4000) NOT NULL ,
> [retailPrice] [money] NOT NULL ,
> [oemPrice] [money] NULL ,
> [hasImage] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblShoppingCarts] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [sessionid] [bigint] NOT NULL ,
> [publishdate] [smalldatetime] NOT NULL ,
> [modifieddate] [smalldatetime] NULL ,
> [productid] [int] NOT NULL ,
> [quantity] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE PROCEDURE spGetShoppingCarts
> @.id int = NULL,
> @.sessionid bigint = NULL
> AS
> -- Get all shopping carts
> IF (@.id IS NULL) AND (@.sessionid IS NULL)
> BEGIN
> SELECT
> sc.[id],
> sc.sessionid,
> sc.publishdate,
> sc.modifieddate,
> sc.productid,
> p.[title] AS ProductTitle,
> p.[shorttitle] AS ProductShortTitle,
> sc.quantity,
> COUNT(sc.sessionid) AS ProductCount
> --p.discount,
> --p.quantity AS DiscountQuantity,
> --p.code
> FROM
> tblShoppingCarts sc,
> tblProducts p
> WHERE
> sc.productid = p.[id]
> GROUP BY
> sc.[id],
> --I WANT TO GROUP BY SESSIONID!!!!!!!!!!!!!!!!!! WHY DOESNT THIS
> WORK??
> sc.sessionid,
> sc.publishdate,
> sc.modifieddate,
> sc.productid,
> p.title,
> p.shorttitle,
> sc.quantity
> ORDER BY
> sc.[modifieddate] DESC
> END
> ELSE
> BEGIN
> -- Get shopping cart by session
> IF (@.id IS NULL)
>
> BEGIN
> SELECT
> sc.[id],
> sc.publishdate,
> sc.modifieddate,
> sc.productid,
> p.[title] AS ProductTitle,
> p.[shorttitle] AS ProductShortTitle,
> sc.quantity,
> ProductCount = ( SELECT COUNT(sc.productid) FROM tblShoppingCarts sc
> WHERE sc.sessionid = @.sessionid )
> --p.discount,
> --p.quantity AS DiscountQuantity,
> --p.code
> FROM
> tblShoppingCarts sc,
> tblProducts p
> WHERE
> sc.productid = p.[id]
> AND sc.[sessionid] = @.sessionid
> END
> -- Get shopping cart by id
> ELSE
> BEGIN
> SELECT
> sc.[id],
> sc.publishdate,
> sc.modifieddate,
> sc.productid,
> p.[title] AS ProductTitle,
> p.[shorttitle] AS ProductShortTitle,
> sc.quantity,
> COUNT(sc.productid) AS ProductCount
> --p.discount,
> --p.quantity AS DiscountQuantity,
> --p.code
> FROM
> tblShoppingCarts sc,
> tblProducts p
> WHERE
> sc.productid = p.[id]
> AND sc.[id] = @.id
> GROUP BY
> sc.[id],
> sc.publishdate,
> sc.modifieddate,
> sc.productid,
> p.title,
> p.shorttitle,
> sc.quantity
> END
>
> END
> GO